Random Selection of Data

[Ghost141]

Hi gang, not sure if its even possible with excel formula or VBA but worth a shot. Im looking at being able to pull a series of random data from a list consisting of 4 columns.

The "randomiser" would pull up to 5 random lines per name and all of the line if they have less than 5.

I have provided an example output to help explain.

Thanks so much.

Book4
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Type	Expected Output	ID	Date	Name	Type
2	1234567	4/19/24	Mr A	Black		1234568	4/20/24	Mr A	Pink
3	1234568	4/20/24	Mr A	Pink		1234569	4/21/24	Mr A	Blue
4	1234569	4/21/24	Mr A	Blue		1234570	4/22/24	Mr A	Red
5	1234570	4/22/24	Mr A	Red		1234572	4/24/24	Mr A	Black
6	1234571	4/23/24	Mr A	Red		1234567	4/19/24	Mr A	Black
7	1234572	4/24/24	Mr A	Black		1234573	4/25/24	Mr B	Pink
8	1234573	4/25/24	Mr B	Pink		1234574	4/19/24	Mr B	Blue
9	1234574	4/19/24	Mr B	Blue		1234575	4/20/24	Mr B	Red
10	1234575	4/20/24	Mr B	Red		1234578	4/23/24	Mr C	Red
11	1234576	4/21/24	Mr C	Red		1234580	4/25/24	Mr C	Blue
12	1234577	4/22/24	Mr C	Pink		1234582	4/20/24	Mr C	Red
13	1234578	4/23/24	Mr C	Red		1234584	4/22/24	Mr C	Blue
14	1234579	4/24/24	Mr C	Pink		1234585	4/23/24	Mr C	Black
15	1234580	4/25/24	Mr C	Blue		1234587	4/25/24	Mr D	Blue
16	1234581	4/19/24	Mr C	Red		1234588	4/19/24	Mr D	Red
17	1234582	4/20/24	Mr C	Red		1234589	4/20/24	Mr D	Red
18	1234583	4/21/24	Mr C	Pink		1234591	4/22/24	Mr D	Red
19	1234584	4/22/24	Mr C	Blue		1234592	4/23/24	Mr D	Pink
20	1234585	4/23/24	Mr C	Black		1234593	4/24/24	Mr E	Blue
21	1234586	4/24/24	Mr C	Pink		1234594	4/25/24	Mr E	Red
22	1234587	4/25/24	Mr D	Blue		1234595	4/19/24	Mr E	Red
23	1234588	4/19/24	Mr D	Red		1234597	4/21/24	Mr F	Blue
24	1234589	4/20/24	Mr D	Red		1234598	4/22/24	Mr F	Pink
25	1234590	4/21/24	Mr D	Pink		1234600	4/24/24	Mr F	Red
26	1234591	4/22/24	Mr D	Red		1234602	4/19/24	Mr F	Blue
27	1234592	4/23/24	Mr D	Pink		1234603	4/20/24	Mr F	Red
28	1234593	4/24/24	Mr E	Blue		1234606	4/23/24	Mr G	Blue
29	1234594	4/25/24	Mr E	Red		1234607	4/24/24	Mr G	Red
30	1234595	4/19/24	Mr E	Red		1234608	4/25/24	Mr G	Blue
31	1234596	4/20/24	Mr F	Pink
32	1234597	4/21/24	Mr F	Blue
33	1234598	4/22/24	Mr F	Pink
34	1234599	4/23/24	Mr F	Pink
35	1234600	4/24/24	Mr F	Red
36	1234601	4/25/24	Mr F	Pink
37	1234602	4/19/24	Mr F	Blue
38	1234603	4/20/24	Mr F	Red
39	1234604	4/21/24	Mr F	Red
40	1234605	4/22/24	Mr F	Pink
41	1234606	4/23/24	Mr G	Blue
42	1234607	4/24/24	Mr G	Red
43	1234608	4/25/24	Mr G	Blue
Sheet1

 

[alansidman]

See if this power query solution works for you.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table [ID=number, Date=number, Name=text, Type=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Date", "Type", "Index"}, {"ID", "Date", "Type", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Index] < 6),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

 

[Fluff]

With a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Type	Expected Output	ID	Date	Name	Type
2	1234567	19/04/2024	Mr A	Black		1234572	24/04/2024	Mr A	Black
3	1234568	20/04/2024	Mr A	Pink		1234571	23/04/2024	Mr A	Red
4	1234569	21/04/2024	Mr A	Blue		1234570	22/04/2024	Mr A	Red
5	1234570	22/04/2024	Mr A	Red		1234568	20/04/2024	Mr A	Pink
6	1234571	23/04/2024	Mr A	Red		1234569	21/04/2024	Mr A	Blue
7	1234572	24/04/2024	Mr A	Black		1234574	19/04/2024	Mr B	Blue
8	1234573	25/04/2024	Mr B	Pink		1234575	20/04/2024	Mr B	Red
9	1234574	19/04/2024	Mr B	Blue		1234573	25/04/2024	Mr B	Pink
10	1234575	20/04/2024	Mr B	Red		1234582	20/04/2024	Mr C	Red
11	1234576	21/04/2024	Mr C	Red		1234584	22/04/2024	Mr C	Blue
12	1234577	22/04/2024	Mr C	Pink		1234583	21/04/2024	Mr C	Pink
13	1234578	23/04/2024	Mr C	Red		1234576	21/04/2024	Mr C	Red
14	1234579	24/04/2024	Mr C	Pink		1234578	23/04/2024	Mr C	Red
15	1234580	25/04/2024	Mr C	Blue		1234587	25/04/2024	Mr D	Blue
16	1234581	19/04/2024	Mr C	Red		1234589	20/04/2024	Mr D	Red
17	1234582	20/04/2024	Mr C	Red		1234592	23/04/2024	Mr D	Pink
18	1234583	21/04/2024	Mr C	Pink		1234591	22/04/2024	Mr D	Red
19	1234584	22/04/2024	Mr C	Blue		1234588	19/04/2024	Mr D	Red
20	1234585	23/04/2024	Mr C	Black		1234593	24/04/2024	Mr E	Blue
21	1234586	24/04/2024	Mr C	Pink		1234594	25/04/2024	Mr E	Red
22	1234587	25/04/2024	Mr D	Blue		1234595	19/04/2024	Mr E	Red
23	1234588	19/04/2024	Mr D	Red		1234601	25/04/2024	Mr F	Pink
24	1234589	20/04/2024	Mr D	Red		1234605	22/04/2024	Mr F	Pink
25	1234590	21/04/2024	Mr D	Pink		1234599	23/04/2024	Mr F	Pink
26	1234591	22/04/2024	Mr D	Red		1234600	24/04/2024	Mr F	Red
27	1234592	23/04/2024	Mr D	Pink		1234597	21/04/2024	Mr F	Blue
28	1234593	24/04/2024	Mr E	Blue		1234608	25/04/2024	Mr G	Blue
29	1234594	25/04/2024	Mr E	Red		1234607	24/04/2024	Mr G	Red
30	1234595	19/04/2024	Mr E	Red		1234606	23/04/2024	Mr G	Blue
31	1234596	20/04/2024	Mr F	Pink
32	1234597	21/04/2024	Mr F	Blue
33	1234598	22/04/2024	Mr F	Pink
34	1234599	23/04/2024	Mr F	Pink
35	1234600	24/04/2024	Mr F	Red
36	1234601	25/04/2024	Mr F	Pink
37	1234602	19/04/2024	Mr F	Blue
38	1234603	20/04/2024	Mr F	Red
39	1234604	21/04/2024	Mr F	Red
40	1234605	22/04/2024	Mr F	Pink
41	1234606	23/04/2024	Mr G	Blue
42	1234607	24/04/2024	Mr G	Red
43	1234608	25/04/2024	Mr G	Blue
Sheet5

Cell Formulas
Range	Formula
F1:I30	F1	=REDUCE(A1:D1,UNIQUE(TOCOL(C2:C100,1)),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(A2:D100,C2:C100=y),TAKE(SORTBY(f,RANDARRAY(ROWS(f))),5)))))
Dynamic array formulas.

 

[Ghost141]

With a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Type	Expected Output	ID	Date	Name	Type
2	1234567	19/04/2024	Mr A	Black		1234572	24/04/2024	Mr A	Black
3	1234568	20/04/2024	Mr A	Pink		1234571	23/04/2024	Mr A	Red
4	1234569	21/04/2024	Mr A	Blue		1234570	22/04/2024	Mr A	Red
5	1234570	22/04/2024	Mr A	Red		1234568	20/04/2024	Mr A	Pink
6	1234571	23/04/2024	Mr A	Red		1234569	21/04/2024	Mr A	Blue
7	1234572	24/04/2024	Mr A	Black		1234574	19/04/2024	Mr B	Blue
8	1234573	25/04/2024	Mr B	Pink		1234575	20/04/2024	Mr B	Red
9	1234574	19/04/2024	Mr B	Blue		1234573	25/04/2024	Mr B	Pink
10	1234575	20/04/2024	Mr B	Red		1234582	20/04/2024	Mr C	Red
11	1234576	21/04/2024	Mr C	Red		1234584	22/04/2024	Mr C	Blue
12	1234577	22/04/2024	Mr C	Pink		1234583	21/04/2024	Mr C	Pink
13	1234578	23/04/2024	Mr C	Red		1234576	21/04/2024	Mr C	Red
14	1234579	24/04/2024	Mr C	Pink		1234578	23/04/2024	Mr C	Red
15	1234580	25/04/2024	Mr C	Blue		1234587	25/04/2024	Mr D	Blue
16	1234581	19/04/2024	Mr C	Red		1234589	20/04/2024	Mr D	Red
17	1234582	20/04/2024	Mr C	Red		1234592	23/04/2024	Mr D	Pink
18	1234583	21/04/2024	Mr C	Pink		1234591	22/04/2024	Mr D	Red
19	1234584	22/04/2024	Mr C	Blue		1234588	19/04/2024	Mr D	Red
20	1234585	23/04/2024	Mr C	Black		1234593	24/04/2024	Mr E	Blue
21	1234586	24/04/2024	Mr C	Pink		1234594	25/04/2024	Mr E	Red
22	1234587	25/04/2024	Mr D	Blue		1234595	19/04/2024	Mr E	Red
23	1234588	19/04/2024	Mr D	Red		1234601	25/04/2024	Mr F	Pink
24	1234589	20/04/2024	Mr D	Red		1234605	22/04/2024	Mr F	Pink
25	1234590	21/04/2024	Mr D	Pink		1234599	23/04/2024	Mr F	Pink
26	1234591	22/04/2024	Mr D	Red		1234600	24/04/2024	Mr F	Red
27	1234592	23/04/2024	Mr D	Pink		1234597	21/04/2024	Mr F	Blue
28	1234593	24/04/2024	Mr E	Blue		1234608	25/04/2024	Mr G	Blue
29	1234594	25/04/2024	Mr E	Red		1234607	24/04/2024	Mr G	Red
30	1234595	19/04/2024	Mr E	Red		1234606	23/04/2024	Mr G	Blue
31	1234596	20/04/2024	Mr F	Pink
32	1234597	21/04/2024	Mr F	Blue
33	1234598	22/04/2024	Mr F	Pink
34	1234599	23/04/2024	Mr F	Pink
35	1234600	24/04/2024	Mr F	Red
36	1234601	25/04/2024	Mr F	Pink
37	1234602	19/04/2024	Mr F	Blue
38	1234603	20/04/2024	Mr F	Red
39	1234604	21/04/2024	Mr F	Red
40	1234605	22/04/2024	Mr F	Pink
41	1234606	23/04/2024	Mr G	Blue
42	1234607	24/04/2024	Mr G	Red
43	1234608	25/04/2024	Mr G	Blue
Sheet5

Cell Formulas
Range	Formula
F1:I30	F1	=REDUCE(A1:D1,UNIQUE(TOCOL(C2:C100,1)),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(A2:D100,C2:C100=y),TAKE(SORTBY(f,RANDARRAY(ROWS(f))),5)))))
Dynamic array formulas.

Incredible, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.