Sampling of data using formula or macros

[Sailadarohit]

Book1
	A	B	C
1	U.ID(Not Unique)	Main ID (Unique)	Priority
2	1	10	High
3	1	11	Medium
4	2	12	Medium
5	3	13	Low
6	5	14	High
7	4	15	Low
8	7	16	Medium
9	5	17	High
10	7	18	Medium
11	1	19	Medium
12	2	20	Low
13	8	21	High
14	9	22	Low
15	6	23	Medium
16	5	24	High
17	4	25	Medium
18	2	26	Medium
19	8	27	Low
20	1	28	High
21	6	29	Low
22	9	30	Medium
23	3	31	High
24	6	32	Medium
25	9	33	Medium
26	1	34	Low
27	1	35	High
28	2	36	Low
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet1

There is a use case where we have U.ID which is not unique, main ID which is unique and priority which covers high, medium and low. In a general scenerio we have mutiple Main ID's associated with one U.ID . For audit purpose we require a sample of this data which will have only 3 main ID's associated with each U.ID.
Please note in real scenario this is a huge data set so manually doing this is not possible.
Can anyone help with an excel formula (preferred) or a macro

 

[RobP]

Hi, is this what you mean ? (assuming I used cell "E1" to insert which U. ID I wanted to filter ?)

Book1
	A	B	C	D	E	F	G
1	U.ID(Not Unique)	Main ID (Unique)	Priority		1
2	1	10	High		1	10	High
3	1	11	Medium		1	11	Medium
4	2	12	Medium		1	19	Medium
5	3	13	Low
6	5	14	High
7	4	15	Low
8	7	16	Medium
9	5	17	High
10	7	18	Medium
11	1	19	Medium
12	2	20	Low
13	8	21	High
14	9	22	Low
15	6	23	Medium
16	5	24	High
17	4	25	Medium
18	2	26	Medium
19	8	27	Low
20	1	28	High
21	6	29	Low
22	9	30	Medium
23	3	31	High
24	6	32	Medium
25	9	33	Medium
26	1	34	Low
27	1	35	High
28	2	36	Low
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=TAKE(FILTER(A2:C49,A2:A49=E1),3,)
Dynamic array formulas.

 

[Sailadarohit]

Hi, is this what you mean ? (assuming I used cell "E1" to insert which U. ID I wanted to filter ?)

Book1
	A	B	C	D	E	F	G
1	U.ID(Not Unique)	Main ID (Unique)	Priority		1
2	1	10	High		1	10	High
3	1	11	Medium		1	11	Medium
4	2	12	Medium		1	19	Medium
5	3	13	Low
6	5	14	High
7	4	15	Low
8	7	16	Medium
9	5	17	High
10	7	18	Medium
11	1	19	Medium
12	2	20	Low
13	8	21	High
14	9	22	Low
15	6	23	Medium
16	5	24	High
17	4	25	Medium
18	2	26	Medium
19	8	27	Low
20	1	28	High
21	6	29	Low
22	9	30	Medium
23	3	31	High
24	6	32	Medium
25	9	33	Medium
26	1	34	Low
27	1	35	High
28	2	36	Low
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=TAKE(FILTER(A2:C49,A2:A49=E1),3,)
Dynamic array formulas.

Hi RobP, thanks for the response. Yes the solution is correct but i want the results such that it has all combined the UID's and 3 random main ID's at one place, one below the other. Because in my scenario i have 1000's of UID's so I cannot manually do this

 

[Fluff]

How about, for 365

Fluff.xlsm
	A	B	C	D	E	F	G
1	U.ID(Not Unique)	Main ID (Unique)	Priority
2	1	10	High		1	10	High
3	1	11	Medium		1	11	Medium
4	2	12	Medium		1	19	Medium
5	3	13	Low		2	12	Medium
6	5	14	High		2	20	Low
7	4	15	Low		2	26	Medium
8	7	16	Medium		3	13	Low
9	5	17	High		3	31	High
10	7	18	Medium		3	37	Medium
11	1	19	Medium		4	15	Low
12	2	20	Low		4	25	Medium
13	8	21	High		4	39	Medium
14	9	22	Low		5	14	High
15	6	23	Medium		5	17	High
16	5	24	High		5	24	High
17	4	25	Medium		6	23	Medium
18	2	26	Medium		6	29	Low
19	8	27	Low		6	32	Medium
20	1	28	High		7	16	Medium
21	6	29	Low		7	18	Medium
22	9	30	Medium		7	40	Medium
23	3	31	High		8	21	High
24	6	32	Medium		8	27	Low
25	9	33	Medium		8	45	High
26	1	34	Low		9	22	Low
27	1	35	High		9	30	Medium
28	2	36	Low		9	33	Medium
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet7

Cell Formulas
Range	Formula
E2:G28	E2	=LET(d,FILTER(A2:C1000,A2:A1000<>""),r,ROWS(d),SORT(FILTER(d,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(d,,1)=TOROW(INDEX(d,,1))),SEQUENCE(r,,,0))<=3)))
Dynamic array formulas.

 

[Sailadarohit]

How about, for 365

Fluff.xlsm
	A	B	C	D	E	F	G
1	U.ID(Not Unique)	Main ID (Unique)	Priority
2	1	10	High		1	10	High
3	1	11	Medium		1	11	Medium
4	2	12	Medium		1	19	Medium
5	3	13	Low		2	12	Medium
6	5	14	High		2	20	Low
7	4	15	Low		2	26	Medium
8	7	16	Medium		3	13	Low
9	5	17	High		3	31	High
10	7	18	Medium		3	37	Medium
11	1	19	Medium		4	15	Low
12	2	20	Low		4	25	Medium
13	8	21	High		4	39	Medium
14	9	22	Low		5	14	High
15	6	23	Medium		5	17	High
16	5	24	High		5	24	High
17	4	25	Medium		6	23	Medium
18	2	26	Medium		6	29	Low
19	8	27	Low		6	32	Medium
20	1	28	High		7	16	Medium
21	6	29	Low		7	18	Medium
22	9	30	Medium		7	40	Medium
23	3	31	High		8	21	High
24	6	32	Medium		8	27	Low
25	9	33	Medium		8	45	High
26	1	34	Low		9	22	Low
27	1	35	High		9	30	Medium
28	2	36	Low		9	33	Medium
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet7

Cell Formulas
Range	Formula
E2:G28	E2	=LET(d,FILTER(A2:C1000,A2:A1000<>""),r,ROWS(d),SORT(FILTER(d,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(d,,1)=TOROW(INDEX(d,,1))),SEQUENCE(r,,,0))<=3)))
Dynamic array formulas.

Thanks Fluff for the solution, is there any way the formula picks up random 3 Main ID instead of first 3. Currently, it is taking only top 3, which might make the samples skewed

 

[Fluff]

How about

=LET(d,FILTER(A2:C1000,A2:A1000<>""),r,ROWS(d),s,SORTBY(d,RANDARRAY(r)),SORT(FILTER(s,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(s,,1)=TOROW(INDEX(s,,1))),SEQUENCE(r,,,0))<=3)))

Although this is volatile & will recalculate whenever any cell is changed or calculates.

 

[Sailadarohit]

How about

=LET(d,FILTER(A2:C1000,A2:A1000<>""),r,ROWS(d),s,SORTBY(d,RANDARRAY(r)),SORT(FILTER(s,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(s,,1)=TOROW(INDEX(s,,1))),SEQUENCE(r,,,0))<=3)))

Although this is volatile & will recalculate whenever any cell is changed or calculates.

sure will try this but when i am trying to run this formula for 5 lakh rows its throwing an error stating "Excel ran out of resources while attempting to calculate one or more formulas"

 

[Fluff]

For that number of rows, I'm not surprised. You will need a macro.

 

[Sailadarohit]

For that number of rows, I'm not surprised. You will need a macro.

If possible, Can you please help with that.
TIA

 

[Fluff]

I don't do much with VBA anymore, but will have a look if I get time & nobody else steps in.