smozgur
BatCoder
- Joined
- Feb 28, 2002
- Messages
- 2,656
INSERTBLANKAT function inserts blank cells at every X row in a given range.
The mini-sheet - G:O columns - shows the steps to build the final Lambda formula.
The mini-sheet - G:O columns - shows the steps to build the final Lambda formula.
Excel Formula:
=LAMBDA(data,atRow,
LET(rows, ROWS(data),
cols, COLUMNS(data),
rowsWithBlanks, SEQUENCE(rows+rows/(atRow-1)),
blankRow,MOD(rowsWithBlanks,atRow),
rowIndex, rowsWithBlanks-INT(rowsWithBlanks/atRow),
IF(blankRow,INDEX(data,rowIndex, SEQUENCE(,cols)),"")
))
INSERTATBLANK.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | Field1 | Field2 | Field3 | At Row | Rows | Cols | Rows with Blanks | 0 is blank row | Row Index | Result | Lambda function | ||||||||||
2 | January | 65 | 165 | 3 | 12 | 3 | 1 | 1 | 1 | January | 65 | 165 | January | 65 | 165 | ||||||
3 | February | 66 | 166 | 2 | 2 | 2 | February | 66 | 166 | February | 66 | 166 | |||||||||
4 | March | 67 | 167 | 3 | 0 | 2 | |||||||||||||||
5 | April | 68 | 168 | 4 | 1 | 3 | March | 67 | 167 | March | 67 | 167 | |||||||||
6 | May | 69 | 169 | 5 | 2 | 4 | April | 68 | 168 | April | 68 | 168 | |||||||||
7 | June | 70 | 170 | 6 | 0 | 4 | |||||||||||||||
8 | July | 72 | 172 | 7 | 1 | 5 | May | 69 | 169 | May | 69 | 169 | |||||||||
9 | August | 73 | 173 | 8 | 2 | 6 | June | 70 | 170 | June | 70 | 170 | |||||||||
10 | September | 69 | 169 | 9 | 0 | 6 | |||||||||||||||
11 | October | 70 | 170 | 10 | 1 | 7 | July | 72 | 172 | July | 72 | 172 | |||||||||
12 | November | 72 | 172 | 11 | 2 | 8 | August | 73 | 173 | August | 73 | 173 | |||||||||
13 | December | 73 | 173 | 12 | 0 | 8 | |||||||||||||||
14 | 13 | 1 | 9 | September | 69 | 169 | September | 69 | 169 | ||||||||||||
15 | 14 | 2 | 10 | October | 70 | 170 | October | 70 | 170 | ||||||||||||
16 | 15 | 0 | 10 | ||||||||||||||||||
17 | 16 | 1 | 11 | November | 72 | 172 | November | 72 | 172 | ||||||||||||
18 | 17 | 2 | 12 | December | 73 | 173 | December | 73 | 173 | ||||||||||||
19 | 18 | 0 | 12 | ||||||||||||||||||
20 | |||||||||||||||||||||
INSERTBLANKAT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2 | G2 | =ROWS(A2:C13) |
H2 | H2 | =COLUMNS(A2:C13) |
I2:I19 | I2 | =SEQUENCE(G2+G2/(E2-1)) |
J2:J19 | J2 | =MOD(I2#,E2) |
K2:K19 | K2 | =I2#-INT(I2#/E2) |
M2:O19 | M2 | = IF(J2#,INDEX(A2:C13,K2#, SEQUENCE(,H2)),"") |
Q2:S19 | Q2 | =LAMBDA(data,atRow, LET(rows, ROWS(data), cols, COLUMNS(data), rowsWithBlanks, SEQUENCE(rows+rows/(atRow-1)), blankRow,MOD(rowsWithBlanks,atRow), rowIndex, rowsWithBlanks-INT(rowsWithBlanks/atRow), IF(blankRow,INDEX(data,rowIndex, SEQUENCE(,cols)),"") ))(A2:C13,E2) |
Dynamic array formulas. |
Upvote
0