Forum Discussion
Solved
Tech Gurus I need your help.
I am trying to figure out a way that if the space on sheet 1 A1 has a 1 it will be copied to sheet 2 A1. If the space on sheet 1 has a 2 it will be copied to sheet2 to A1 and A2. Please tell me there...
Let's say your data are in A1:B6 on Sheet 1:
On the second sheet, enter this formula in A1:
=LET( Counts, 'Sheet 1'!A1:A6, Names, 'Sheet 1'!B1:B6, XLOOKUP(SEQUENCE(SUM(Counts)), SCAN(0, Counts, SUM), Names, , 1))
If you need this capability often then it makes sense to define a Lambda to handle the task:
RepeatRowsλ =
LAMBDA(rows, repeat,
LET(
k, ROWS(repeat),
rep, MAX(INDEX(repeat, k),1),
seq, SEQUENCE(rep, , k, 0),
next, TAKE(rows, -k),
repeated, CHOOSEROWS(next, seq),
acc, VSTACK(repeated, rows),
unpack, DROP(acc, -1),
IF(k = 1, unpack, RepeatRowsλ(unpack, DROP(repeat, -1)))
)
);The function can also unpack tables:
If the repetition is not specified for an item or row the function defaults to 1. Attached is a demo.
I appreciate your response, however, I am trying to transfer data from sheet 1 of a spreadsheet to sheet 2. Also, in the demo you sent, if a 0 is entered it throws a huge error.
The function works for me when 0 is entered for an item.
