Forum Discussion
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 is a way to do this easily! I trust you Excel Gurus!
| 1 | James | ||||
| 2 | Steve |
James | ||||
| Steve | ||||
| Steve | ||||
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))
8 Replies
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.
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))I have copied and pasted your solution and I changed the data to fit what I have on my sheet, however, I am getting a syntax error. Your solution makes more sense to me as there are multiple pages to this spread sheet and I am only looking to copy from page 1 to page 2 for my data. Any idea as to what this syntax would be as all I changed were Column and start/end information.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
