Forum Discussion

dweatherley99's avatar
dweatherley99
Copper Contributor
May 05, 2022
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 ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 05, 2022

    dweatherley99 

    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 Sub

    Maybe with this code. You can click the button in cell I3 in the attached file in sheet "Tabelle1" to start the macro.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 07, 2022

dweatherley99 

Please accept my apologies for treating this as a formula challenge rather that a request for help!  Following Patrick2788's use of MAP over a 2D array, I decided to try SCAN, allowing it to run through the entire array row by row switching 1 and 0 every time it came to a cell with a 1.  That almost solved the problem except for the final value of each series which showed as 0.  An OR ('+') operation with the initial range sorted it.

 

//Worksheet formula
= SIGN(rng + SCAN(0,rng, XORλ));
//where
XORλ = LAMBDA(x,y,XOR(x,y))

 

Perhaps reintroducing MAP might offer a more consistent programming approach

 

= --MAP(rng, SCAN(0,rng, XORλ), ORλ)

 

 

Resources