Random select from column A with no duplicates in column B

[mlanescott]

Hello.
My task is to select 5 REPORT IDs (Column A) without having any duplicates by the Creator Name (Column B).

REPORT ID	Creator Name
0003324442	Goofy
0003324445	Goofy
0003324447	Goofy
0003324449	Goofy
0003326011	Goofy
0003326012	Goofy
0003326013	Goofy
0003326827	Odie
0003328843	Odie
0003328849	Pluto
0003328853	Odie
0003328856	Pluto
0003328964	Pluto
0003329061	Pluto
0003329289	Muttley
0003330238	Scoobert
0003330239	Odie
0003330251	Scoobert
0003330261	Odie
0003330272	Muttley
0003330274	Muttley
0003330302	Scoobert
0003330303	Scoobert
0003330304	Snoopy
0003330305	Snoopy
0003330343	Snoopy
0003333755	Bolt
0003333839	Odie
0003333840	Odie

I appreciate you sharing your knowledge!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you have the LET function in your Excel 365?
If so, would this suffice?

20 12 15.xlsm
	A	B	C	D	E
1	REPORT ID	Creator Name
2	0003324442	Goofy		0003330239	Odie
3	0003324445	Goofy		0003326013	Goofy
4	0003324447	Goofy		0003330274	Muttley
5	0003324449	Goofy		0003330302	Scoobert
6	0003326011	Goofy		0003330343	Snoopy
7	0003326012	Goofy
8	0003326013	Goofy
9	0003326827	Odie
10	0003328843	Odie
11	0003328849	Pluto
12	0003328853	Odie
13	0003328856	Pluto
14	0003328964	Pluto
15	0003329061	Pluto
16	0003329289	Muttley
17	0003330238	Scoobert
18	0003330239	Odie
19	0003330251	Scoobert
20	0003330261	Odie
21	0003330272	Muttley
22	0003330274	Muttley
23	0003330302	Scoobert
24	0003330303	Scoobert
25	0003330304	Snoopy
26	0003330305	Snoopy
27	0003330343	Snoopy
28	0003333755	Bolt
29	0003333839	Odie
30	0003333840	Odie
Random List

Cell Formulas
Range	Formula
D2:E6	D2	=LET(fltr,FILTER(A$2:B$30,ISNA(MATCH(B$2:B$30,E$1:E1,0))),INDEX(fltr,RANDBETWEEN(1,ROWS(fltr)),0))
Dynamic array formulas.

If no LET function then try

=INDEX(FILTER(A$2:B$30,ISNA(MATCH(B$2:B$30,E$1:E1,0))),RANDBETWEEN(1,ROWS(FILTER(A$2:B$30,ISNA(MATCH(B$2:B$30,E$1:E1,0))))),0)

 

[mlanescott]

Apparently I do not have the LET function. Bummer. Using the alternative formula is returning duplicates from Column B.
I will look into what it takes to get the LET function going. In the meantime, is there anything that can be done about duplicates from Column B?
BTW - I feel so honored to have you answering my questions. Thank you!!

 

[Fluff]

Peter's 2nd formula works for me without any duplicates from col B
Did you put it in D2 & fill down?

+Fluff v2.xlsm
	A	B	C	D	E
1	REPORT ID	Creator Name
2	3324442	Goofy		3328964	Pluto
3	3324445	Goofy		3326827	Odie
4	3324447	Goofy		3330272	Muttley
5	3324449	Goofy		3324442	Goofy
6	3326011	Goofy		3330304	Snoopy
7	3326012	Goofy
8	3326013	Goofy
9	3326827	Odie
10	3328843	Odie
11	3328849	Pluto
12	3328853	Odie
13	3328856	Pluto
14	3328964	Pluto
15	3329061	Pluto
16	3329289	Muttley
17	3330238	Scoobert
18	3330239	Odie
19	3330251	Scoobert
20	3330261	Odie
21	3330272	Muttley
22	3330274	Muttley
23	3330302	Scoobert
24	3330303	Scoobert
25	3330304	Snoopy
26	3330305	Snoopy
27	3330343	Snoopy
28	3333755	Bolt
29	3333839	Odie
30	3333840	Odie
31
Data

Cell Formulas
Range	Formula
D2:E6	D2	=INDEX(FILTER(A$2:B$30,ISNA(MATCH(B$2:B$30,E$1:E1,0))),RANDBETWEEN(1,ROWS(FILTER(A$2:B$30,ISNA(MATCH(B$2:B$30,E$1:E1,0))))),0)
Dynamic array formulas.

 

[mlanescott]

Fluff - you are correct. I applied the formula to my actual data so the output got a little squirrely. Regrettably, I do not entirely understand what the formula is saying so I have to keep it simple. With that in mind, I readjusted and all is well. I'm absolutely ecstatic to have this little trick up my sleeve and can hardly thank Mr. Excel enough!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Peter_SSs]

... can hardly thank Mr. Excel enough!

You just did.

I do not entirely understand what the formula is saying

See if we can explain a little. Take this much smaller example where we want 3 at random without creator repeats. Clearly that means we will need 1 drawn from each creator.

20 12 15.xlsm
	A	B	C	D	E
1	REPORT ID	Creator Name
2	0003324442	Goofy		0003329289	Muttley
3	0003324445	Goofy		0003330238	Scoobert
4	0003326013	Goofy		0003326013	Goofy
5	0003329289	Muttley
6	0003330238	Scoobert
7	0003330251	Scoobert
Random List (3)

Cell Formulas
Range	Formula
D2:E4	D2	=INDEX(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E1,0))),RANDBETWEEN(1,ROWS(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E1,0))))),0)
Dynamic array formulas.

Suppose we accept the first result in row 2 and look the formula in D3
=INDEX(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E2,0))),RANDBETWEEN(1,ROWS(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E2,0))))),0)
The two red parts are identical. The underlined part tries to match everything in B2:B7 to values in E1:E2 so it will match the one Muttley but produce errors for the other 5 rows.
The FILTER then will return the original array but with only the error rows, that is 5 rows. The RANDBETWEEN picks a random number from 1 to 5 and chooses that row from this new 5-row array. In this case it chose the first Scoobert row.

Now look at the formula in D4
=INDEX(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E3,0))),RANDBETWEEN(1,ROWS(FILTER(A$2:B$7,ISNA(MATCH(B$2:B$7,E$1:E3,0))))),0)
MATCH tries to match everything in B2:B7 to what is in E1:E3 so it will match the 1 Muttley row and the 2 Scoobert rows and error for the 3 Goofy rows.
The FILTER then will return the original array but with only the error (Goofy) rows (3 rows). The RANDBETWEEN picks a random number from 1 to 3 and chooses that row from this new 3-row array. In this case it chose the third Goofy row.