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
