Here is my example table:
ID | Enroll Year | #Years |
---|---|---|
23001 | 2022 | 1 |
23006 | 2022 | 1 |
23015 | 2022 | 1 |
23014 | 2021 | 2 |
23004 | 2020 | 3 |
I would like to use the "#years" column to define how many rows should exist for each ID and then increase the "enroll year" column by 1 for each additional row. So, the end table from this sample of data would look like:
ID | Enroll Year |
---|---|
23001 | 2022 |
23006 | 2022 |
23015 | 2022 |
23014 | 2021 |
23014 | 2022 |
23004 | 2020 |
23004 | 2021 |
23004 | 2022 |
I imagine that I will need to use a reduce
function, but I cannot figure out how to write this. Any help would be so appreciated!
Thank you!
Use reduce()
and vstack()
, like this:
=let(
data, filter(A2:B, C2:C),
reduce(A1:B1, sequence(rows(data)), lambda(a, i, let(
vstack(a, map(sequence(index(C2:C, i), 1, 0), lambda(j, hstack(
index(data, i, 1), index(data, i, 2) + j
))))
)))
)
See let(), filter(), reduce(), sequence(), vstack(), map(), and index().