google-sheetsgoogle-sheets-formula

Is there a formula to expand data to multiple rows per entry?


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!


Solution

  • 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().