Extract random list with criteria excel

[IceSnakeR]

Hi all,

I have been doing a lot of research in the last two weeks and it seems I cannot find an adequate solution for my need. I have attached a photo with a replica of the sheet I am using, in my file I have a lot more rows (about 12000 rows). The information in column C:C are unique sale id's, the information in column D:D is a list of names (about 89 names) that will repeat. I need to extract randomly a list of sale ID but I don't want to receive as a result two or more sale IDs for the same name as it is shown in the photo bellow.

I was able to find a formula that extracts the random list based on a value that I manually insert in cell D3 where I specify how many sale ID to extract. I was able to achieve this with the formula >>> =INDEX(SORTBY(C3:C31,RANDARRAY(ROWS(C3:C31))),SEQUENCE(D3)) <<< in cell E3 which correctly extracts the random sales id. The column F:F is not required; I am using it to perform an xlookup to understand if a sale person appears twice in the list.

Thank you in advance,
Chris

 

[JvdV]

This should do it:

Book1
	A	B	C	D	E	F	G
1
2		Person	ID
3		JOHN	123		8	JOHN	307
4		ADAM	146			JULIE	422
5		MARY	169			MARY	169
6		JANE	192			JANE	744
7		JOANNE	215			JOANNE	215
8		JULIE	238			MACK	261
9		MACK	261			ADAM	698
10		LUCY	284			LUCY	284
11		JOHN	307
12		ADAM	330
13		MARY	353
14		JANE	376
15		JOANNE	399
16		JULIE	422
17		MACK	445
18		LUCY	468
19		JOHN	491
20		ADAM	514
21		MARY	537
22		JANE	560
23		JOANNE	583
24		JULIE	606
25		MACK	629
26		LUCY	652
27		JOHN	675
28		ADAM	698
29		MARY	721
30		JANE	744
31		JOANNE	767
Sheet2

Cell Formulas
Range	Formula
F3:G10	F3	=LET(x,B3:C31,y,SORTBY(x,RANDARRAY(ROWS(x))),z,TAKE(y,,1),TAKE(FILTER(y,XMATCH(z,z)=SEQUENCE(ROWS(x))),E3))
Dynamic array formulas.

The idea here is to first sort the entire array randomly, filter the result based on 1st occurences of the names in the 1st column and take the appropriate amount of rows from the top of this filtered array.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[IceSnakeR]

This should do it:

Book1
	A	B	C	D	E	F	G
1
2		Person	ID
3		JOHN	123		8	JOHN	307
4		ADAM	146			JULIE	422
5		MARY	169			MARY	169
6		JANE	192			JANE	744
7		JOANNE	215			JOANNE	215
8		JULIE	238			MACK	261
9		MACK	261			ADAM	698
10		LUCY	284			LUCY	284
11		JOHN	307
12		ADAM	330
13		MARY	353
14		JANE	376
15		JOANNE	399
16		JULIE	422
17		MACK	445
18		LUCY	468
19		JOHN	491
20		ADAM	514
21		MARY	537
22		JANE	560
23		JOANNE	583
24		JULIE	606
25		MACK	629
26		LUCY	652
27		JOHN	675
28		ADAM	698
29		MARY	721
30		JANE	744
31		JOANNE	767
Sheet2

Cell Formulas
Range	Formula
F3:G10	F3	=LET(x,B3:C31,y,SORTBY(x,RANDARRAY(ROWS(x))),z,TAKE(y,,1),TAKE(FILTER(y,XMATCH(z,z)=SEQUENCE(ROWS(x))),E3))
Dynamic array formulas.

The idea here is to first sort the entire array randomly, filter the result based on 1st occurences of the names in the 1st column and take the appropriate amount of rows from the top of this filtered array.

Hi JvdV, this works for me. Thank you!