Outputting a cell x number of times in an array format

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

Using Google Sheets:
Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1.

I've created a dummy file to show what I'm trying to do and shared it for viewing:

I can do a single item using:

=transpose(split(rept(Sheet1!A2&";",Sheet1!B2),";"))

But can't figure out how to keep going for the rest of the items in the list.
Any help would be great.

Best Answer

=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(
 INDIRECT("Sheet1!A2:A"&COUNTA(A1:A)), ",")&",", 
 INDIRECT("Sheet1!B2:B"&COUNTA(B1:B))))), ","))

0

Related Topic