I have to work with a large table on Google Sheet containing, roughly, weights associated with a day (and other superfluous data). It looks like this:
| Date | Weight 1 | Weight 2 | Weight 3 |
|---|---|---|---|
| 01/01/22 | 20 | 22 | 21 |
| 01/02/22 | 19 | 25 |
A date, and multiple weights associated.
Ideally, I would need an intermediate table that includes all the columns of the initial table, but with one weight per row. like this one for example:
| Date | Weight |
|---|---|
| 01/01/22 | 20 |
| 01/01/22 | 22 |
| 01/01/22 | 21 |
| 01/02/22 | 19 |
| 01/02/22 | 25 |
I tried several methods to filter this table and retrieve all the weights for each date independently. Index/match, filter, querry... I couldn't get what I needed.
Do you know if there is a formula that would allow me to obtain this second table?
CodePudding user response：
try:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(if(len(B2:D)*len(A2:A),A2:A22&"|"&B2:D22,)),"|"),"Select * Where Col2 is not null"))
CodePudding user response：
try:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A&""&B2:D), ""), "where Col2 is not null", ))