+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

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

    Please try at G15

    =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. #3
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

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

    Quote Originally Posted by Bo_Ry View Post
    Please try at G15

    =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.
    Attached Files Attached Files
    Last edited by ryguy1188; 06-28-2019 at 03:01 PM. Reason: Added Attachment

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

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

    Please try at G15

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Press Ctrl+Shift+Enter

  5. #5
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

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

    Quote Originally Posted by Bo_Ry View Post
    Please try at G15

    Formula: copy to clipboard
    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).
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    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. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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.
    Attached Files Attached Files

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

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

    Please try at Q9
    =IFERROR(VLOOKUP(L9,Table2,2,0),INDEX(Table1[Name],MOD(AGGREGATE(15,6,(COUNTIF(Q$8:Q8,Table1[Name])+COUNTIFS(Table2[Holiday Name],Table1[Name],Table2[Holiday Week],">"&L9))*100+ROW(Table1[Name]),1),100)-ROW(Table1[#Headers])))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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. #13
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

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

    This should fix
    Q9

    =IFERROR(VLOOKUP(L9,Table2,2,0),INDEX(Table1[Name],MOD(AGGREGATE(15,6,(COUNTIF(Q$8:Q8,Table1[Name])+COUNTIFS(Table2[Holiday Name],Table1[Name],Table2[Holiday Week],">"&L9,Table2[Holiday Week],"<"&L9+14))*100+ROW(Table1[Name]),1),100)-ROW(Table1[#Headers])))

    You may play with number +14 in red
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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. #15
    Registered User
    Join Date
    06-28-2019
    Location
    USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    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?
    Last edited by ryguy1188; 01-23-2020 at 07:11 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Yearly Roster - Day count to date.
    By natkemon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-03-2021, 05:56 PM
  2. Rolling Monthly Roster linked to In or Out Occupancy
    By Bendeguz in forum Excel General
    Replies: 9
    Last Post: 04-21-2019, 03:01 PM
  3. Replies: 3
    Last Post: 04-10-2017, 10:54 AM
  4. How to find sheet having the fewest rows of data?
    By LineOfBestFit in forum Excel General
    Replies: 1
    Last Post: 03-31-2016, 04:47 PM
  5. Formula to count clinics that have at least one student listed in roster
    By owenpga in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-29-2016, 09:11 PM
  6. roster making macro as per shift count
    By DJ1890 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2013, 09:48 AM
  7. Rolling roster
    By Hagar42 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-17-2012, 09:32 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1