Randomly Select Based on a value of another Cell

[manny805]

Hello-

Trying to create a formula that randomly selects a tracking ID for each associate. In addition, make an alternate selection, but it can't be a duplicate of first random selection. Was thinking a VLOOKUP & RANDBETWEEN combo, but stumped. Not sure if I have to go through VBA route?

Any suggestions would be appreciated. Sample workbook attached.

Regards,
Manny

Random_Selection.xlsx
	B	C	D	E	F	G
3
4	Associate	Tracking ID		Associate	Random Tracking ID Selection	Alternate Random Tracking ID Selection (can't be a duplicate of previous slection)
5	Smith, Mary	TRK65152		Smith, Mary
6	Smith, Mary	TRK74183		Jones, Walter
7	Smith, Mary	TRK35154		Williams, Tony
8	Smith, Mary	TRK23117
9	Smith, Mary	TRK11889
10	Jones, Walter	TRK17364
11	Jones, Walter	TRK91736
12	Jones, Walter	TRK88234
13	Jones, Walter	TRK80012
14	Jones, Walter	TRK55874
15	Williams, Tony	TRK58142
16	Williams, Tony	TRK47336
17	Williams, Tony	TRK13254
18	Williams, Tony	TRK28596
19	Williams, Tony	TRK33371
20
Random_Selection

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Associate	Tracking ID		Associate	Random Tracking ID Selection	Alternate Random Tracking ID Selection
3	Smith, Mary	TRK65152		Smith, Mary	TRK35154	TRK74183
4	Smith, Mary	TRK74183		Jones, Walter	TRK88234	TRK91736
5	Smith, Mary	TRK35154		Williams, Tony	TRK58142	TRK28596
6	Smith, Mary	TRK23117
7	Smith, Mary	TRK11889
8	Jones, Walter	TRK17364
9	Jones, Walter	TRK91736
10	Jones, Walter	TRK88234
11	Jones, Walter	TRK80012
12	Jones, Walter	TRK55874
13	Williams, Tony	TRK58142
14	Williams, Tony	TRK47336
15	Williams, Tony	TRK13254
16	Williams, Tony	TRK28596
17	Williams, Tony	TRK33371
18
Summary

Cell Formulas
Range	Formula
E3:F5	E3	=LET(Fltr,FILTER($B$3:$B$17,$A$3:$A$17=D3),INDEX(SORTBY(Fltr,RANDARRAY(ROWS(Fltr))),{1,2}))
Dynamic array formulas.

 

[manny805]

WOW!! Thank you! Exactly what I was looking for.

Take care,
Manny

 

[Fluff]

You're welcome & thanks for the feedback.