Excel formula help - populating cells between two cells that contain '1' by row

[dweatherley99]

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 '1' and before the last '1'. For example, the first row would have Tue and Wed populated with '1' because Mon and Thu have '1' filled. Each row has 2 '1' values.

Does anyone have a solution for this? This is just a small snippet of my data, and it is a big dataset to I would like to automate it rather than doing it manually. Thanks in advance.

Mon	Tue	Wed	Thu	Fri	Sat	Sun
1			1
	1	1
	1				1
1		1
1				1
			1		1
				1		1
1		1
	1		1
	1				1

[#2 · Accepted solution]

@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.

[#3]

@dweatherley99 

Attached is a formula solution.  Must have access to LAMBDA.

I'm curious what @Peter Bartholomew  might come up with in this situation.

[#4]

Hi,

Thanks ever so much for this, this worked perfectly for what I wanted, appreciate it!

[#5]

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!

[#6]

@Patrick2788 

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

[#7]

@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λ)

[#8]

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.