Pulling a 5% sample from a table

[iosiflupis]

Hello all,

I have a table that will have over 800 records when done. We need to be able to select a RANDOM 5% sample for auditing. The data that we will be using to determine selection will be in column A:A (Tracking Number). I have been looking at RANDOM, RANDBETWEEN, and INDEX. I also searched the library here, seeing that most of the results were using VBA. I would like to stay away from VBA if possible as I will not be the only one using this tabel.

The table has columns A - O, but as I stated column A is where the unique information resides.


Thank you all,
Joseph Carney

 

[SanjayGMusafir]

This can be one way. I have used multiple columns to show you the steps.

But the idea is

• to add two helper columns
• In First Column we put RANDOMBETWEEN(1,Total Cases)
• Then Copy and Paste Values of that column in that column itself.
• Sort Data by this new column
• Add another column which selects 5% of cases top to bottom based on number calculated in a cell
• You get most random 5% cases to audit.

Try it, if it works for you. it took me just 2 minutes to do

All Records.xlsb
	A	B	C	D	E	F
1	ID	Random	PasteValue	To Audit		1
2	ID 028	1	1	Yes
3	ID 030	3	3	No
4	ID 033	4	4	No
5	ID 026	5	5	No
6	ID 036	5	5	No
7	ID 025	6	6	No
8	ID 034	6	6	No
9	ID 020	7	7	No
10	ID 011	11	11	No
11	ID 013	11	11	No
12	ID 005	12	12	No
13	ID 002	13	13	No
14	ID 027	13	13	No
15	ID 037	13	13	No
16	ID 021	14	14	No
17	ID 001	15	15	No
18	ID 032	17	17	No
19	ID 031	18	18	No
20	ID 007	19	19	No
21	ID 009	21	21	No
22	ID 008	22	22	No
23	ID 014	22	22	No
24	ID 018	22	22	No
25	ID 022	22	22	No
26	ID 012	24	24	No
27	ID 015	24	24	No
28	ID 019	24	24	No
29	ID 017	25	25	No
30	ID 024	29	29	No
31	ID 039	30	30	No
32	ID 003	31	31	No
33	ID 006	31	31	No
34	ID 023	31	31	No
35	ID 010	32	32	No
36	ID 016	34	34	No
37	ID 038	35	35	No
38	ID 035	37	37	No
39	ID 004	38	38	No
40	ID 029	39	39	No
Sheet7

Cell Formulas
Range	Formula
F1	F1	=INT(COUNTA(Table43[ID])*5%)
B2:B40	B2	=RANDBETWEEN(1,COUNTA([ID]))
D2:D40	D2	=IF(COUNTA($C$2:C2)<=$F$1,"Yes","No")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A40	Expression	=D2="Yes"	text	NO

 

[JamesCanale]

In this case, I have 100 records in column A.

MrExcelPlayground12.xlsx
	A	B	C
1	Tracking Number		Audit
2	1000		1012
3	1001		1030
4	1002		1060
5	1003		1082
6	1004		1083
7	1005
8	1006
9	1007
10	1008
11	1009
12	1010
13	1011
14	1012
15	1013
16	1014
17	1015
18	1016
19	1017
20	1018
21	1019
22	1020
23	1021
24	1022
25	1023
26	1024
27	1025
28	1026
Sheet24

Cell Formulas
Range	Formula
C2:C6	C2	=LET(b,COUNT(A:A),r,RANDARRAY(b,1,0,1,FALSE),a,INDEX(A:A,SEQUENCE(b,1,2)),SORT(INDEX(SORTBY(a,r),SEQUENCE(0.05*b))))
Dynamic array formulas.

 

[Fluff]

another option if yo have the new TAKE function.

Fluff.xlsm
	A	B	C
1	Tracking Number		Audit
2	1000		1118
3	1001		1198
4	1002		1246
5	1003		1108
6	1004		1094
7	1005		1048
8	1006		1286
9	1007		1211
10	1008		1239
11	1009		1277
12	1010		1250
13	1011		1163
14	1012		1162
15	1013		1005
16	1014		1197
17	1015
18	1016
19	1017
20	1018
21	1019
22	1020
23	1021
24	1022
25	1023
26	1024
27	1025
28	1026
29	1027
30	1028
Data

Cell Formulas
Range	Formula
A2:A301	A2	=SEQUENCE(300,,1000)
C2:C16	C2	=LET(f,FILTER(A2:A1000,A2:A1000<>""),TAKE(SORTBY(f,RANDARRAY(ROWS(f))),ROWS(f)*0.05))
Dynamic array formulas.

 

[iosiflupis]

Well, I had three answers within 2 hours. Thank you all for your assistance!

 

[SanjayGMusafir]

Well, I had three answers within 2 hours. Thank you all for your assistance!

Glad to help you

 

[Fluff]

Glad we could help & thanks for the feedback.