Assign Rows to a Year with Criteria

[msnyder]

I have a list of locations that I have sorted and would like to assign them to a specific year based on a separate input table.

In the attached Locations by Year file I have an example of the input template I'm using.  My rows that I am trying to assign to a year have the number of locations contained in each row.

For example, I want to assign rows 1-5 to 2022 that have 5, 4, 4, 4, and 3 locations respectively.  Then assign the following two rows to 2023 that have 8 and 7 locations respectively.  And so on down the line until all locations have a year.

I have started by taking the Locations by Year input and generating enough rows to sufficiently capture all rows.  How can I do the rolling total with the logic to flip to the next year once rolling locations is greater than assigned locations for the year?

[DataNath]

Apologies if I've misunderstood but I'm reading this as you want to generate all 185 rows as you already have, but have the Year splits reflected in the right place? Something like this?

[OllieClarke · Accepted solution]

Hi @msnyder 

I had to guess at your other data set, but this is what I came up with

Not 100% it's the best solution as there's a big cross-join in the middle, but it does mean you don't need a batch macro.
It also assumes that the total number of locations is completely covered by the total locations in your year table.

Hope that helps,

Ollie

[msnyder]

Based on the input file I am generating ~100 rows per year (too many but will remove unused rows later).

My other input looks like the green highlighted section below, which is already sorted based on another criteria.

I want to assign the year to each of my other rows(yellow highlight), until my total sum of 'Locations' for that assigned year meets/exceeds the desired number of 'Locations', and then assign the next year to the following row until all rows have been assigned a year.