Could anyone guide me on this data manipulation task? I have data like this in a spreadsheet:
(CSV)
Date,Slot 1,Slot 2,Slot 3
1/1/2024,AB,CD,EF
1/2/2024,AB
1/3/2024,CD,EF
...
I'd like it displayed as:
1/1/2024,AB
1/1/2024,CD
1/1/2024,EF
1/2/2024,AB
1/3/2024,CD
1/3/2024,EF
Sample spreadsheet https://docs.google.com/spreadsheets/d/12_5QORbukvGv_0LWQY8rDXoHa1hMGHYrzSg9Wv2LQ7E/edit?usp=sharing
You can use:
=ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A7&"❅"&B2:D7),"❅"),"where Col2<>''"))