Assigning Round Robin Based on Criteria

[Kumara_faith]

Hi,

I have the attached table:

The first table is the raw data. The second table shows the available pool of case workers.

I am trying to assign the available case workers to the list of patients in table 1. The assigning should in sequence so that all case workers get equal number of patients. However, if there is a case with the same patient name within the same date, then these case should be assigned to the same case worker.

An example of the correct results is in the table below in column C. Is there a formula that could be used in column C to achieve this ? Appreciate all the help.

I have across post here:

https://www.mrexcel.com/board/thread.../#post-5954921

[Kaper]

At first glance it looked easy, especially with helper column, see Shit1.
in D2 1, and two formulas (yellow cells) copied down as needed (countifs, sum, index, mod and counta in not complicated mix).
But then I noticed, the input table is arranged not by date, but by the case number, and it means non-uniform load for workers.

With excel 365 specific functions (unique, filter, sort) it can be done even as single column solutoion, but it's not short formula. Could be shorter if date and patient were in adjactent columns, without case numn=ber in between.
Anyway - for D2 and copy down:

Please Login or Register  to view this content.

I think that time to time the sheet shall be cleaned from outdated cases, to keep it working with reasonable speed. The old data can be kept in the archive,
For such cleaning, I'd temporarily sort the table on date and caseID ascending (see copy od sheet) and copy special as values and as formats only to archive top rows but ending with a row where assigned worker is the last worker from the workers list. Then remove these rows from main table and finally (see second copy of that seet sort back on case ID

Final comments: I expect using LET could make the formula a bit more readible, also a VBA solution could be a good idea, especially if based on worksheet_change event for columns A:C and E.

[josephteh]

With "Patient Name" in column C. "Case Worker" in column D and "Case Worker Pool" in column F,
D2=IF(C1=$C$1,$F$2,IF(D1=LOOKUP(2,1/($F$2:$F$6<>""),$F$2:$F$6),$F$2,IF(C2=C1,D1,INDEX($F$2:$F$6,MATCH(D1,$F$2:$F$6,0)+1))))

[Kumara_faith]

Thank you Kaper and josephteh. Appreciate your patience and solution. Have a great day ahead.