'Tranform Dataset With Two Dates In Different Cells into Series or Sequence (Google Sheet)
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!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|