Hello,
Attached is a stripped down demo spreadsheet.
Normally there can be up to 140 teams and 8 categories.
Managers select names for events.
Ideally the first name selected from a team in each category is the fastest and they get placed in the centre lanes.
Have used random to distribute teams so that team A in the demo is not always in lane 6 of heat 1.
It also helps distribute teams across the heats.
However, at present I can end up with say 4 heats and 5 members of team A distributed across only 2 heats.
To avoid complaints I would like to move on from random and have a formula/mechanism to be able to check if an existing team member is already in the heat before inserting the next entry.
It could be that the distribution is triggered off a button click which would hopefully avoid endless background loops.
It could be that the distribution occurs on a change to the heat but this may result in a back ground lop.
Needs to run on Excel 2016 with 4 Gb's of RAM.
I started to look at if statements but quickly became over whelmed.
I would very much appreciate guidance of on how to achieve this distribution.
Thank you.
Bookmarks