Forum Discussion
Solved
Excel formula help - populating cells between two cells that contain '1' by row
Hi all, Currently I am working with a very large dataset which looks like this (see photo attached). I am trying to find a way to automatically fill cells with a '1' in rows after the first ...
Sub one() Dim j As Integer Dim i As Integer Dim z As Integer Dim k As Integer For i = 2 To 11 For j = 7 To 1 Step -1 If Cells(i, j).Value = 1 Then z = j For k = 1 To 7 If Cells(i, k).Value = 1 And k < z Then Cells(i, k + 1).Value = 1 If k = z Then Exit For Else End If Else End If Next k Else End If Next j Next i End SubMaybe with this code. You can click the button in cell I3 in the attached file in sheet "Tabelle1" to start the macro.
Attached is a formula solution. Must have access to LAMBDA.
I'm curious what PeterBartholomew1 might come up with in this situation.
There is much of your solution in this but the detail is different. The solution clearly is a nested array so the problem is working around that. I adopted your solution of calculating start and end columns as distinct 1D arrays but using XMATCH to locate the first and last instances of '1'. Again, I adopted your idea of treating the next stage of the problem as a 2D array problem, but was able to dispense with the MAP and use a simple array formula.
= LET( k, SEQUENCE(1,7), start, BYROW(rng, Matchλ(1)), end, BYROW(rng, Matchλ(-1)), (k>=start)*(k<=end) ) //where Matchλ = LAMBDA(mode, LAMBDA(row, XMATCH(1,row,,mode)));- I like it. It's a clever workaround using SEQUENCE to create the array. I was trying to avoid using INDEX but used it because I couldn't line up (or maybe nest is a better word) 'start' and 'end' with the COLUMN result of each cell as MAP worked its way through the array.
- Thanks for the formula, Patrick. I did manage to achieve my goal by using VBA which the above commenter assisted me with, but I have taken note of your formula for future reference. Much appreciated!
