May 05 2022 08:42 AM
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 |
May 05 2022 10:10 AM
SolutionSub 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.
May 05 2022 02:45 PM
Attached is a formula solution. Must have access to LAMBDA.
I'm curious what @Peter Bartholomew might come up with in this situation.
May 06 2022 03:50 AM
May 06 2022 03:51 AM
May 06 2022 03:48 PM
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)));
May 07 2022 01:39 AM - edited May 07 2022 01:43 AM
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λ)
May 07 2022 04:11 AM
May 05 2022 10:10 AM
SolutionSub 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.