In Google Sheets I have a set of data with IDs in Column A and Columns B onwards are date related with Row 1 being the date and Rows 2 onwards being a number value for that ID and date combination.
For example:
| ID | 2023-01-01 | 2023-01-02 | 2023-01-03 | 2023-01-04 | 2023-01-05 | 2023-01-06 | 2023-01-07 |
|---|---|---|---|---|---|---|---|
| 100001 | 9 | 10 | 11 | 3 | 12 | 3 | |
| 100002 | 7 | 11 | 11 | 18 | 16 | 12 | |
| 100003 | 3 | 20 | 8 | 7 | 11 | 4 | |
| 100004 | 7 | 19 | 12 | 4 | 12 | ||
| 100005 | 8 | 17 | 3 | 5 | 4 | 16 | 16 |
I would like to create a formula in a second sheet to filter and convert this data into the following format so that each ID and date combination is an individual row with the ID in column 1, the date in column 2, and the number value in column 3, skipping the rows where the number value is blank, like this:
| ID | Date | Value |
|---|---|---|
| 100001 | 2023-01-01 | 9 |
| 100001 | 2023-01-02 | 10 |
| 100001 | 2023-01-04 | 11 |
| 100001 | 2023-01-05 | 3 |
| 100001 | 2023-01-06 | 12 |
| 100001 | 2023-01-07 | 3 |
| 100002 | 2023-01-01 | 7 |
| 100002 | 2023-01-02 | 11 |
| 100002 | 2023-01-03 | 11 |
| 100002 | 2023-01-04 | 18 |
| 100002 | 2023-01-06 | 16 |
| 100002 | 2023-01-07 | 12 |
I’ve created an example sheet with some sample data in the input and desired output formats here: https://docs.google.com/spreadsheets/d/1qtKCjFhxCqjpiOK0DnOWjptq_UYLTXlfS5cXM0wiCrw/edit#gid=0
I have achieved this before but for the life of me I cannot remember how, and I have searched a fair bit but can’t find a solution.
I believe it requires an array formula with split and transpose.
Any help would be greatly appreciated!
>Solution :
You may try:
=let(Σ,reduce({"ID","Date","Value"},sequence(counta('Source Data'!A2:A)),lambda(a,c,{a;
reduce(wraprows(,3,),sequence(counta('Source Data'!B1:1)),lambda(x,y,{x;index('Source Data'!A2:A,c),index('Source Data'!B1:1,y),index('Source Data'!B2:1000,c,y)}))})),
filter(Σ,index(Σ,,3)<>""))
