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)));