Forum Discussion
Excel formula help - populating cells between two cells that contain '1' by row
| 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 |
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.
7 Replies
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λ)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!
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.
- Hi,
Thanks ever so much for this, this worked perfectly for what I wanted, appreciate it!
