# A rolling roster that selects a name with the fewest count.

1. ## A rolling roster that selects a name with the fewest count.

Hello everyone,
I am stumped on how I should go about tackling this. I will like to avoid macros or VBAs.

Basically I am creating a roster that changes daily. To be fair, the roster needs to rotate among everyone equally. For holiday's, the roster also needs to rotate equally but still have the total count to be equal among everyone at the end of the year. I do not want the roster list to have back to back names.

My solution attempt.
1) first start with holidays and have a index mod function select a name from the input list, and populate it on the holiday list. (works)
2) Then Put these names that were selected on holidays, into the daily roster. (works)
3) Now Count the roster and see how many times each person is listed. (works)
4) Then using MIN function, find the name who has the fewest count. (works)
5) Put the selection from #4 into the roster and repeat. (Doesn't work).

The uploaded sheet is very descriptive of what I need to happen, but I just can't get this to work. My method seems to have a circular issue that could be solved with a different function, but I do not know which route to go.

Please let me know if you can think of a solution that can avoid macros or VBAs.

-best regards

2. ## Re: A rolling roster that selects a name with the fewest count.

=IFNA(VLOOKUP(E15,Table15,2,0),INDEX(B:B,SMALL(IF(ISERROR(MATCH(Table16[Name],IF(COUNTIF([Date],Table15[Holiday Day]),Table15[Assigned Name]),)),ROW(Table16)),SUMPRODUCT(1-(COUNTIF(Table15[Holiday Day],E\$15:E15))))))

Press Ctrl+Shift+Enter

3. ## Re: A rolling roster that selects a name with the fewest count.

Originally Posted by Bo_Ry

=IFNA(VLOOKUP(E15,Table15,2,0),INDEX(B:B,SMALL(IF(ISERROR(MATCH(Table16[Name],IF(COUNTIF([Date],Table15[Holiday Day]),Table15[Assigned Name]),)),ROW(Table16)),SUMPRODUCT(1-(COUNTIF(Table15[Holiday Day],E\$15:E15))))))

Press Ctrl+Shift+Enter
Thanks for the reply! Ok, I have tried it in G15 and it works, however after every name has been used once with a count of 1, it returns an #NUM! error. I need the roster to repeat filling up the list by selecting more people for continuing dates. For example, I need 365 days to be filled up with only 11 names.

4. ## Re: A rolling roster that selects a name with the fewest count.

Formula:
`Please Login or Register  to view this content.`

Press Ctrl+Shift+Enter

5. ## Re: A rolling roster that selects a name with the fewest count.

Originally Posted by Bo_Ry

Formula:
`Please Login or Register  to view this content.`

Press Ctrl+Shift+Enter
Thank you for the reply. I've been trying to mess with it, but now I noticed the name count is not equal when I expanded it to include more days and names. For example, now some names are counted 3 while others are counted 5. See attached sheet. (Note: I changed my strategy going from days to week numbers instead).

6. ## Re: A rolling roster that selects a name with the fewest count.

Hello ryguy1188 and Welcome to Excel Forum.
This proposal employs two helper columns (I:J) which may be moved and/or hidden for aesthetic purposes.
Column I is populated using: =IF(MOD(ROW(A1),MAX(Table164[Name]))=0,MAX(Table164[Name]),MOD(ROW(A1),MAX(Table164[Name])))
Column J is populated using: =IFERROR(IF(INDEX(Table153[Assigned Name],MATCH(Table52[@Week],Table153[Week],0))=I16,I16+1,""),"")
Column G is populated using: =IF(J16<>"",J16,IF(J15<>"",I15,I16))
Note that minimal testing has been conducted so that more testing is recommended.
Let us know if you have any questions.

7. ## Re: A rolling roster that selects a name with the fewest count.

JeteMc,
Thank you for helping and the welcome. It appears that you have a different approach to the problem, however I am not following you.
1) Can you please explain the purpose of column J?
2) The Assigned Name on the holiday section column C, is not populating the assigned Name in Column G.

8. ## Re: A rolling roster that selects a name with the fewest count.

I was thinking that the holiday list was for persons that should not be assigned a certain week. I see now that is incorrect.
Column J's purpose was to replace the person if that person ended up assigned the week of their holiday.

9. ## Re: A rolling roster that selects a name with the fewest count.

See if this resolves the issue:
1. Column I is populated using: =IFERROR(INDEX(Table153[Assigned Name],MATCH(F16,Table153[Week],0)),"")
2. Column J is populated using: =IF(I16="",MAX(SUM(MAX(I\$16:I\$69),1),SUM(MAX(J\$15:J15),1)),"")
3. Column K is populated using: =IFERROR(MOD(J16,MAX(Table164[Name])),I16)
4. Column G is populated using: =IF(K16=0,18,K16)
Let us know if you have any questions.

10. ## Re: A rolling roster that selects a name with the fewest count.

JeteMc,
I'm sorry I think there might be some confusion between your approach and the original goal.
This is what I am trying to do. (I have attached a new version of the sheet to simplify things and explain them).
Steps:
1) Place Holiday List Override into schedule.
2) Evaluate total Name Count on entire schedule, and then select a name from the roster with the fewest count.
3) Repeat step 2 for each cell going forward. Count, Evaluate, then Select.

I've having difficulty actually counting all the names in the column and then selecting the count with the fewest for the entire column.

12. ## Re: A rolling roster that selects a name with the fewest count.

Bo_Ry,
This is almost exactly what I needed, however something strange with the code is happening with Name_14 and Name_15. They are almost back to back starting at week 49 50 51 and 52. It might be because of logic of taking the name with the most count.

Are you familiar with another method to achieve a more uniform spaced out roster like the Desired Output column R?

It might have to be like before where I have to count only the previous cells, but for some reason it wasn't expanding when I altered the list. Could you try that method to this latest sheet?

13. ## Re: A rolling roster that selects a name with the fewest count.

This should fix
Q9

You may play with number +14 in red

14. ## Re: A rolling roster that selects a name with the fewest count.

Perfect! It works! Thank you so much for helping everyone! Ya'll are the best.

15. ## Re: A rolling roster that selects a name with the fewest count.

Bo_Ry , I would like to add new feature but I do not know where to start. I've tried to decompose the formula, but it is very complicated to me.

Basically I would like have the option for a name to be skipped in the next rotation, if the name falls on a holiday week.
For example, there are 5 names in this rotating list. Name_04 falls on a holiday, so the next rotation then skips that name and continues. Then once the 2nd rotation is complete, Name_04 is added back into the third rotation and beyond.

.................Name - Week Number
Rotation 1) Name_01 - Week 1
.................Name_02 - Week 2
.................Name_03 - Week 3
.................Name_04 - Week 4 (Holiday)
.................Name_05 - Week 5
Rotation 2) Name_01 - Week 6
.................Name_02 - Week 7
.................Name_03 - Week 8
.................Name_05- Week 9
Rotation 3) Name_01 - Week 10
.................Name_02 - Week 11
.................Name_03 - Week 12
.................Name_04 - Week 13
.................Name_05 - Week 14
Rotation 4) Name_01 - Week 15
.................Name_02 - Week 16
.................Name_03 - Week 17
.................Name_04 - Week 18
.................Name_05 - Week 19
Rotation 5) Continued....
................
.................

Do you think this is possible to add it to this existing formula?

There are currently 1 users browsing this thread. (0 members and 1 guests)