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 |
I took it a step further if such things are possible. I split the quantity into individual colors (I will have about 17 colors) and would like to generate a list of my ITEMS in the same way like before but with a color assigned to them. I used your function @player0 but I had to do it with two steps, with indirect data. I could merge it in one-line function but it would be reeeeealy long function (add your "INDEX" formula in every "FILTER" arguments).
Is simplier way to do it?
example:
| ITEMS | COLOR 1 | COLOR 2 | COLOR 3 | COLOR 4 | ect. |
|---|---|---|---|---|---|
| A | 1 | ||||
| B | 2 | ||||
| C | 1 | 1 | |||
| D | |||||
| E | 1 | 2 | |||
| F | 1 |
so another sheet should show:
| ITEMS |
|---|
| A1 |
| B3 |
| B3 |
| C2 |
| C4 |
| E3 |
| E4 |
| E4 |
| F1 |
my solution
=INDEX(FLATTEN(TRIM(SPLIT(QUERY(REPT(A2:A10&" "&B1&"×",B2:B10),,9^9), "×"))))
=FILTER({F1:F6;G1:G6;H1:H6}, LEN({F1:F6;G1:G6;H1:H6}))
