So I have a table like the one below that has data inserted daily.
| Job Name | 3/11/2022 | 3/12/2022 | 3/13/2022 | ... |
|---|---|---|---|---|
| Job 1 | 25 | 30 | 10 | ... |
| Job 2 | 0 | 40 | 95 | ... |
| Job 3 | 15 | 0 | 62 | ... |
| Job 4 | 85 | 75 | 38 | ... |
I want to convert it to rows of data like the below. This needs to happen automatically. It needs to skip all 0 values. So Job 2 on the 11th and Job 3 on the 12th are not included.
| Date | Job Name | Usage |
|---|---|---|
| 3/11/2022 | Job 1 | 25 |
| 3/11/2022 | Job 3 | 15 |
| 3/11/2022 | Job 4 | 85 |
| 3/12/2022 | Job 1 | 30 |
| 3/12/2022 | Job 2 | 40 |
| 3/12/2022 | Job 4 | 75 |
| 3/13/2022 | Job 1 | 10 |
| 3/13/2022 | Job 2 | 95 |
| 3/13/2022 | Job 3 | 62 |
| 3/13/2022 | Job 4 | 38 |
CodePudding user response：
Try
=query(arrayformula(split(flatten(B1:D1&"~"&A2:A5&"~"&B2:D5),"~")),"where Col3 <> 0")
