Assign Round Robin Based on Criteria

[kumara_faith]

Hi,

I have the following table:

Book1
	B	C	D
3	Date	Case Number	Patient Name
4	28/9/2022	LA1457	Javier Rami
5	28/9/2022	LA1458	Javier Rami
6	29/9/2022	LA1459	Ila Riley
7	30/9/2022	LA1460	Hailey Amber
8	30/9/2022	LA1461	Hailey Amber
9	2/10/2022	LA1462	Meera Thor
10	29/9/2022	LA1463	Rambert Hool
11	30/9/2022	LA1464	Oolea Romeo
12	29/9/2022	LA1465	Ashely Huth
13	30/9/2022	LA1466	Boneer Vice
Sheet1

Book1
	G
3	Case Worker Pool
4	Alan
5	Michael
6	Johnny
7	Reena
8	Joey
Sheet1

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 number 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 E. Is there a formula that could be used in column E to achieve this ? Appreciate all the help.

Book1
	B	C	D	E	F	G
3	Date	Case Number	Patient Name	Case Worker		Case Worker Pool
4	28/9/2022	LA1457	Javier Rami	Alan		Alan
5	28/9/2022	LA1458	Javier Rami	Alan		Michael
6	29/9/2022	LA1459	Ila Riley	Michael		Johnny
7	30/9/2022	LA1460	Hailey Amber	Johnny		Reena
8	30/9/2022	LA1461	Hailey Amber	Reena		Joey
9	2/10/2022	LA1462	Meera Thor	Joey
10	29/9/2022	LA1463	Rambert Hool	Michael
11	30/9/2022	LA1464	Oolea Romeo	Johnny
12	29/9/2022	LA1465	Ashely Huth	Reena
13	30/9/2022	LA1466	Boneer Vice	Joey
Sheet1

 

[kumara_faith]

Hi,

Sorry- The sentence below should read as "....same patient name" instead of "..same patient number..."

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 number within the same date, then these case should be assigned to the same case worker.

 

[kumara_faith]

Hi,

Appreciate any help if this is possible.

 

[kumara_faith]

Hi,

Appreciate any help.

 

[kumara_faith]

Hi,

I have cross poste here:

Assigning Round Robin Based on Criteria

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

www.excelforum.com

 

[Kaper]

The formula for D2:

=INDEX(E:E,MOD(MATCH(1,(A2=FILTER(SORT(UNIQUE(FILTER(A:C,{1,0,1}))),{1,0}))*(C2=FILTER(SORT(UNIQUE(FILTER(A:C,{1,0,1}))),{0,1})),0)-1,COUNTA(E:E)-1)+2)

Full explanation and workbook in the crossposted thread on excelforum.com (mentioned above).

 

[kumara_faith]

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