Home > database >  Google Sheets: Dynamic array formula that repeats a range n times based on another range / specific
Google Sheets: Dynamic array formula that repeats a range n times based on another range / specific

Time:01-11

Hoping to get a single dynamic formula in Google Sheets that gives me the following:

Scenario 1

Input:

| ID | Date                   |
| 1  | Jan 1, 2023            |
| 2  | Jan 2, 2023            |
| 3  | Jan 3, 2023            |
|    | Jan 4, 2023            |
|    | Jan 5, 2023            |

Desired output:

ID  Date
----------------
1   Jan 1, 2023
1   Jan 2, 2023
1   Jan 3, 2023
1   Jan 4, 2023
1   Jan 5, 2023
2   Jan 1, 2023
2   Jan 2, 2023
2   Jan 3, 2023
2   Jan 4, 2023
2   Jan 5, 2023
3   Jan 1, 2023
3   Jan 2, 2023
3   Jan 3, 2023
3   Jan 4, 2023
3   Jan 5, 2023

Scenario 2 (preferred)

Input:

ID  Start Date  End Date
----------------------------
1   Jan 1, 2023 Jan 10, 2023
2   Jan 5, 2023 Jan 10, 2023
3   Jan 8, 2023 Jan 10, 2023

Desired output:

ID  Date
---------------
1   Jan 1, 2023
1   Jan 2, 2023
1   Jan 3, 2023
1   Jan 4, 2023
1   Jan 5, 2023
1   Jan 6, 2023
1   Jan 7, 2023
1   Jan 8, 2023
1   Jan 9, 2023
1   Jan 10, 2023
2   Jan 5, 2023
2   Jan 6, 2023
2   Jan 7, 2023
2   Jan 8, 2023
2   Jan 9, 2023
2   Jan 10, 2023
3   Jan 8, 2023
3   Jan 9, 2023
3   Jan 10, 2023

You may see this enter image description here

  • Related