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?

Locations by Year.xlsx

OllieClarke

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

years.yxmd