I am trying to transform dataset in google sheet that contains two dates in different cells into data series with single column date. Here is the ilustration:
Original Dataset:
| ID | Start Date | End Date | Name | Category | Subcategory |
|---|---|---|---|---|---|
| 10001 | 09/07/2021 | 11/07/2021 | Jona | A | A1 |
| 10002 | 10/07/2021 | 11/07/2021 | Adam | B | B1 |
Needed:
| ID | Date | Name | Category | Subcategory |
|---|---|---|---|---|
| 10001 | 09/07/2021 | Jona | A | A1 |
| 10001 | 10/07/2021 | Jona | A | A1 |
| 10001 | 11/07/2021 | Jona | A | A1 |
| 10002 | 10/07/2021 | Adam | B | B1 |
| 10002 | 11/07/2021 | Adam | B | B1 |
I need the second data structure to avoid using index and match to lookup record with multiple criteria that takes long process (over 3000 rows). Further, I need it so that I can apply date range filter in Google Data Studio that requires dates series.
All I have tried is to use Sequence function to populate dates between two given dates, but it only gives me series of dates without other cells data (I refer to this method).
Do you have any idea? I will appreciate your help. Thanks a lot!
Update Question July 20, 2022:
Is it possible to do the opposite? I mean transform data series into date range (transform the second table into the first table). Really appreciate your help!
