randbetween / index with conditions and without duplicates

[LovelyDay83]

Hi,

I have the following issue, any help would be much appreciated as I am not an excel expert at all...

I have a list of cases B4:B1003 with corresponding owners C4:C1003, few rows are empty (different numbers of cases each month).
For quality check, I want to assign 10% of total number of cases (number of cases to be reviewed calculated in I2) to 5 team members (table E4:I23).
The team members must not review their own cases (column C), they should not review a case already assigned to another team member and there must not be a duplicate in the total list of cases to be reviewed.

I finally did :
for the first team mamber =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 1";$C$4:$C$1003;"<>")))
the second one =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 2";$C$4:$C$1003;"<>";$C$4:$C$1003;"<>$E$4:$E$23")))
the third one =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 3";$C$4:$C$1003;"<>";$C$4:$C$1003;"<>$E$4:$F$23")))
etc... adding a column each time to avoid preassigned cases to other team members in the last condition criteria of the formula

I tryed with index, randbetween, coutifs, rank as I am not good in VBA... I am a bit lost and need your help because using a large amount of case I get some mistakes (duplicates or case assigned to the owner for review).

Many thanks in advance for your support!

[vba_php]

well, I might be able to offer something here. you said:

For quality check, I want to assign 10% of total number of cases (number of cases to be reviewed calculated in I2) to 5 team members (table E4:I23).

that is fine, and perfectly understandable, however you have RANDBETWEEN in your calc'd formulas and nests. the purpose of [RAND]-oriented anything in terms of a function is to return randomness in some form. see here for your function:

https://support.microsoft.com/en-us/...f-a469ab381685

I did not replicate your issue, however are you sure you want randomization in your formulas? it doesn't sound like you need it and/or it should even be relevant.

[ChemistB]

I used two helper helper columns to randomize and rank. I use RAND() rather than RANDBETWEEN() because RAND produces a number with 15 digits and the likelihood of duplicates is extremely rare. The only issue with my formula is cosmetic as it will leave blank spaces where the value matches up to the same user. I tried using AGGREGATE but it ended up giving me duplicates. I'm sure one of the other gurus can come up with a better solution.
In D4 copied down to D1003

Please Login or Register  to view this content.

In E4, copied down to E1003

Please Login or Register  to view this content.

Then in G4 copied across and down

Please Login or Register  to view this content.

Does that work for you?

[MrShorty]

You don't share any details of what you have tried, so you might have already tried this. I would approach this problem as a "sorting" problem rather than a randbetween problem.

1) Assign each case a random unique number (I usually use RAND()).
1a) To deal with a variable length list, nest inside of an IF() function to only assign a random number when there is a case. L4 =IF(A4="","",RAND())
2) Rank/Sort the list of random numbers M4 =IFERROR(RANK(L4,$L$4:$L$1003),"")
3) A lookup function to find the top 10 random numbers
3a) Enter the numbers 1 to 10 in N4:N13
3b) In O4, =MATCH(N4,$M$4:$M$1003,0)
3c) One or more INDEX() functions to extract the case information. P4 =INDEX($B$4:$B$1003,O4)
4) Assign users to the cases, with a check to make sure you don't assign a user his/her own case.

Will something like that work for you?

[hrlngrv]

To rephrase, you have case IDs in col B and employee IDs for the employee responsible for each case in col C. You want to sample 10% of cases, assigning them to any of the employees EXCEPT the one shown in col C, and you want to avoid the same case being assigned to multiple employees.

Add a single column of additional formulas. That formula should be =RAND() . I'll assume this is in col D, and that B4:D1003 is named CaseTable.

I'll assume you have a list of IDs for all the employees who could review cases in one column and the formula =RAND() in the column immediately to the right of those IDs, and that this 2-column range is named EmployeeTable. If you don't have such a table, create one.

Then in another worksheet, you can pull a sample of 10% of cases using formulas like this.

A3: =MATCH(LARGE(INDEX(CaseTable,0,3),ROWS(A$3:A3)),INDEX(CaseTable,0,3),0)
B3: =INDEX(CaseTable,$A3,1)
C3: =INDEX(CaseTable,$A3,2)
D3: =INDEX(EmployeeTable,MATCH(LARGE(INDEX(INDEX(EmployeeTable,0,2)*(INDEX(EmployeeTable,0,1)<>C3),0),1+MOD(ROWS(D$3:D3)-1,ROWS(EmployeeTable)-1)),INDEX(EmployeeTable,0,2),0),1)

Fill A3:D3 down into A4:D102. Each month, recalculate the workbook, then copy this table, A3:D102, and paste special as values somewhere else. That'd be the review schedule for the month, with meaningless number in col A, case ID in col B, ID for the employee responsible for that case in col C, and ID for the employee to review that case in col D.

The idea here is that you want random sampling without replacement. That's most easily accomplished by sorting in random order, which is what the formulas above would do.

Note: this does NOT ensure reviewing employees (D3:D102) each review the same number of cases. However, you should be able to balance review loads manually from the pasted special as value table.

[Greg M]

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M


P. S. Sorry! I misread your original post - this does not prevent the same case being assigned to more than one reviewer.

I'll see if I can post something to address this.

[Greg M]

Hi again,

Take a look at the attached version of your workbook and see if it works better than the one I posted previously. The workbook uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[LovelyDay83]

Many thanks to all of you for your great support!! I will work on it following your recommendations and get back to you if needed.
Thanks again, you are awesome!!!

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M