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!
Bookmarks