Google-sheets – How to convert an array of unique records into an array where records are repeated N times

concatenateformulasgoogle sheetsgoogle-sheets-arrayformula

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. For example, this would be the source table:

Item            Quantity
------------------------
Whiteboard      3
A4 paper ream   4
Cash box        2

And the destination table:

Item    
--------------
Whiteboard
Whiteboard
Whiteboard
A4 paper ream
A4 paper ream
A4 paper ream
A4 paper ream
Cash box
Cash box

I've tried using a combination of the REPT, SPLIT and TRANSPOSE function, to repeat the string N number of times, then split it, and then transpose it – the problem is that this will only work for one record, because multiple records will try and overwrite each other. Because the total number of records will be over 36,000, it's not feasible to do this manually.

Is there a formula or otherwise that can achieve this result?

Bonus question: Is there also a way of preserving information in the source table on the destination table? For example, if my source table has a location field for each record (which it does), can I use a formula to copy this information to each repeated record? For example:

Item            Quantity    Location
------------------------------------------
Whiteboard      3           Shelf 1
A4 paper ream   4           Box 2
Cash box        2           Shelf 7

Item            Location  
------------------------
Whiteboard      Shelf 1
Whiteboard      Shelf 1
Whiteboard      Shelf 1
A4 paper ream   Box 2
A4 paper ream   Box 2
A4 paper ream   Box 2
A4 paper ream   Box 2
Cash box        Shelf 7
Cash box        Shelf 7

Best Answer

=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(
 INDIRECT("A2:A"&COUNTA(A2:A)+1), ",")&",", 
 INDIRECT("B2:B"&COUNTA(B2:B)+1)))), ","))

0


=ARRAYFORMULA({TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
 INDIRECT("A2:B"&COUNTA(A2:A)+1), ",")&",", 
 INDIRECT("B2:B"&COUNTA(B2:B)+1))), ",")), TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
 INDIRECT("C2:C"&COUNTA(A2:A)+1), ",")&",", 
 INDIRECT("B2:B"&COUNTA(B2:B)+1))), ","))})

enter image description here


=ARRAYFORMULA(SUBSTITUTE({TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
 INDIRECT("A2:B"&COUNTA(A2:A)+1), ",")&",", 
 INDIRECT("B2:B"&COUNTA(B2:B)+1))), ",")), TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
 IF(INDIRECT("C2:C"&COUNTA(A2:A)+1)="", "♦", INDIRECT("C2:C"&COUNTA(A2:A)+1)), ",")&",", 
 INDIRECT("B2:B"&COUNTA(B2:B)+1))), ","))}, "♦", ""))

0