CODEWITHSUNDEEP
google-sheetsconcatenationtransposegoogle-sheets-formulaarray-formulas
Chris Barrett
Chris Barrett

Reputation: 601

Repeat a row depending on Quantity

I have a table of data formatted as:

+--------------+------+----------+
| Manufacturer | Item | Quantity |
+--------------+------+----------+

I need to re-arrange this so that each item has its own row so:

+--------------+------+----------+
| Manufacturer | Item | Quantity |
+--------------+------+----------+
| Coca Cola    | Coke |       4  |
+--------------+------+----------+

Becomes:

+--------------+------+
| Manufacturer | Item |
+--------------+------+
| Coca Cola    | Coke |
| Coca Cola    | Coke |
| Coca Cola    | Coke |
| Coca Cola    | Coke |
+--------------+------+

I've written this which works for a single line from the source table:

=split(transpose(split(rept(Join("|",A5:B5)&"$$", C5),"$$")),"|")

But I'm unsure how to expand this to an entire table. Doing this:

=arrayformula(split(transpose(split(rept(Join("|",A5:B)&"$$", C5:C),"$$")),"|"))

..just gives an error

JOIN range must be a single row or a single column.

Any help would be much appreciated

Upvotes: 1

Views: 84

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
 INDIRECT("A1:A"&COUNTA(A1:A))&"♦"&
 INDIRECT("B1:B"&COUNTA(B1:B)), ",")&",", 
 INDIRECT("C1:C"&COUNTA(C1:C)))), ",")), "♦"))

0

Upvotes: 1

Related Questions