Search code examples
google-sheetsgoogle-sheets-formulaspreadsheettransposeflatten

How to filter data with multiple duplicate quantity


I have a table in Google Sheet, where are listing items (A-Z). I can fill qty of this items in column QTY. How to list in another sheet all of qantitied items and appear as many times as is entered (sometimes cell qty is empty). I tryed with FILTER and ARRAYFORMULA but without result.

example:

ITEMS QTY
A 1
B 3
C
D
E 2
F 1

in another sheet should be filtered:

ITEMS
A
B
B
B
E
E
F

Solution

  • use:

    =INDEX(FLATTEN(TRIM(SPLIT(QUERY(REPT(A1:A10&"×", B1:B10),,9^9), "×"))))
    

    enter image description here