Random employee assignment to accounts that stops assigning Employees when the count reaches 25

[Lisa116]

I use Office 365, and have a sheet with15,000 accounts that need to be assigned to employees. I am randomly assigning employee IDs to each row and once the employee ID is used 25 times the formula needs to exclude the employee ID. I need help excluding that employee ID from the random selection.

 

[JamesCanale]

Maybe you can make use of this:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
1	Account No.	Assigned Emp		16	Emp List
2	1	Gustof			John	15
3	2	Gustof			Fred	16
4	3	John			Mary	13
5	4	Jamie			Sally	14
6	5	Jamie			Jamie	16
7	6	Gustof			Gustof	16
8	7	John
9	8	Mary
10	9	Jamie
11	10	John
12	11	Gustof
13	12	Mary
14	13	John
15	14	Sally
16	15	Jamie
17	16	Sally
18	17	Jamie
19	18	Mary
20	19	Gustof
21	20	John
22	21	Gustof
23	22	Gustof
24	23	John
25	24	Sally
26	25	Gustof
27	26	Mary
28	27	Mary
Sheet15

Cell Formulas
Range	Formula
A2:A91	A2	=SEQUENCE(90)
F2:F7	F2	=COUNTIF(B2:B91,E2:E7)
B2	B2	=LET(EL,$E$2:$E$7,b,INT(RAND()*ROWS(EL)+1),INDEX(EL,b))
B3:B28	B3	=LET(z,$D$1,el,$E$2:$E$7,a,B$2:B2,b,COUNTIF(a,el),c,FILTER(el,b<z),d,INT(RAND()*ROWS(c)+1),IFERROR(INDEX(c,d),"Out of Employees"))
Dynamic array formulas.

 

[Lisa116]

Thank you so much for your quick response. I like the advise, but I am getting the IFERROR response. Please show me what I am doing wrong. In my first row I have the formula:
LET(A,FILTER(BP7:EP7,BP7:EP7>0),B,COUNTA(BP7:EP7),C,RANDBETWEEN(1,B),D,CHOOSECOLS(A,C),D)

The second row I used what you gave me, with a small adjustment, because my EEIDs are in columns.
LET(M,25,A,FILTER(BP8:EP8,BP8:EP8>0),B,AI$7:AI7,C,COUNTIF(B,A),D,FILTER(A,B<M),E,INT(RAND()*COLUMNS(A)+1),IFERROR(INDEX(D,E),"NO BPA"))

 

[JamesCanale]

Maybe:

LET(M,25,A,FILTER(BP8:EP8,BP8:EP8>0),B,AI$7:AI7,C,COUNTIF(B,A),D,FILTER(A,B<M),E,INT(RAND()*COLUMNS(D)+1),IFERROR(INDEX(D,E),"NO BPA"))

"D" will be smaller than "A", so you might get an index that goes too big. But it should work some of the time (most of the time). If it never works, that is troubling.

What I like about "LET" is that you can diagnose it pretty well. change it to:
LET(M,25,A,FILTER(BP8:EP8,BP8:EP8>0),B,AI$7:AI7,C,COUNTIF(B,A),D,FILTER(A,B<M),E,INT(RAND()*COLUMNS(D)+1),F,IFERROR(INDEX(D,E),"NO BPA"),F)

And then replace that last "F" with all the other variables until the problem becomes obvious.

 

[Lisa116]

Good morning and thank you again for your response. I made a few adjustments and now I am getting a number but not the number I am expecting back. I changed the name value D formula to C<M and changed the RAND() to RANDBETWEEN(). Please help me understand why I am getting 11 instead of a number from Columns BP to BU? None of these numbers have been used more than 25 times. I look forward to your guidance!

 

[Lisa116]

Good morning again! I was able to get the formula to work but on the rows with "NO BPA" there should be one of the numbers to the right of column BP populating the cell. I would understand getting the "NO BPA" later down the sheet. Maybe I am not understand the name value F formula.

 

[JamesCanale]

I think part of the problem is the randbetween instead of rand. It's a good idea, but then multiplying by columns(D) doesn't make sense. And I would randbetween(1,D) not "E". Posting in xl2bb a portion of your stuff might help me with the diagnosing.

 

[Lisa116]

Good evening James. Thank you for all of your help. The changes your stated above made the difference. The random is working perfectly. I cannot thank you enough for helping me. Take care.