Pick 20% random rows from list with criteria

[pasjauo]

Hey guys and girls

We are doing some quality control at work and are working on randomizing our selection of case-files the fastest way.

Right now we have a export from our system that shows the digital case file number and the owner of the case. We want to check, lets say, 20% of each persons cases for errors etc.

The excel file can look something like this but with 1000-1500 cases in every period (with more columns but these are not important):

CASE FILE NUMBER	OWNER
19-2000	Peter
19-2012	Andrea
19-1451	Peter
19-1234	Mitch
19-1513	Bob
19-7464	Mitch
19-2362	Bob
19-3561	Bob
19-1515	Andrea
19-9285	Peter

I would like to randomly check for instance 20% of Peters cases and for that I need the Case file number. I need to check every persons cases, so it is not just Peter's

Is there any way to make this selection fast and painless? I don't mind spending a little time on a solution as long as it will work in the future (we want to do this every month). The most important thing is that my co-workers without any excelknowledge can work in the file.

So far I have played around with the RAND() but there is some degree of work every time we need to do this. A simple copy/paste a new period into a work sheet is what i'm expecting of time to be spent every period.

 

[lrobbo314]

Here is a way using Power Query.

Book2
	A	B	C	D	E
1	CASE FILE NUMBER	OWNER		OWNER	CASE FILE NUMBER
2	19-2000	Peter		Peter	19-2290
3	19-2012	Andrea		Peter	19-2841
4	19-1451	Peter		Peter	19-5430
5	19-1234	Mitch		Peter	19-7862
6	19-1513	Bob		Andrea	19-5893
7	19-7464	Mitch		Andrea	19-7919
8	19-2362	Bob		Andrea	19-1145
9	19-3561	Bob		Andrea	19-1515
10	19-1515	Andrea		Andrea	19-1790
11	19-9285	Peter		Mitch	19-1171
12	19-6343	Mitch		Mitch	19-9839
13	19-6907	Andrea		Mitch	19-7926
14	19-9883	Andrea		Mitch	19-1086
15	19-7807	Bob		Mitch	19-7684
16	19-9512	Peter		Bob	19-9524
17	19-5893	Andrea		Bob	19-2458
18	19-2061	Peter		Bob	19-5461
19	19-8448	Andrea		Bob	19-2362
20	19-7633	Andrea		Bob	19-9339
21	19-7684	Mitch		Bob	19-6786
22	19-3090	Mitch
23	19-6745	Peter
24	19-9524	Bob
25	19-2680	Andrea
26	19-1145	Peter
27	19-1171	Mitch
28	19-9872	Bob
29	19-9981	Bob
30	19-1086	Mitch
31	19-1498	Bob
32	19-2290	Peter
33	19-3809	Andrea
34	19-4622	Mitch
35	19-7862	Peter
36	19-3001	Bob
37	19-2841	Peter
38	19-1552	Bob
39	19-3534	Bob
40	19-2629	Peter
41	19-7156	Bob
42	19-3632	Peter
43	19-7541	Peter
44	19-9181	Bob
45	19-2272	Andrea
46	19-4395	Mitch
47	19-8849	Bob
48	19-6587	Mitch
49	19-5669	Bob
50	19-9482	Peter
51	19-6889	Andrea
52	19-1790	Andrea
53	19-8913	Peter
54	19-3145	Mitch
55	19-9339	Bob
56	19-7344	Bob
57	19-5378	Peter
58	19-9839	Mitch
59	19-5258	Mitch
60	19-3884	Andrea
61	19-3941	Peter
62	19-4995	Mitch
63	19-2458	Bob
64	19-7919	Andrea
65	19-8122	Bob
66	19-9410	Bob
67	19-6661	Andrea
68	19-1056	Bob
69	19-4723	Mitch
70	19-7007	Peter
71	19-3162	Mitch
72	19-5314	Andrea
73	19-9680	Andrea
74	19-5439	Bob
75	19-2118	Andrea
76	19-7279	Bob
77	19-9603	Bob
78	19-7268	Mitch
79	19-5182	Mitch
80	19-6273	Andrea
81	19-5531	Andrea
82	19-3430	Andrea
83	19-6747	Peter
84	19-1145	Andrea
85	19-4139	Andrea
86	19-7830	Bob
87	19-6786	Bob
88	19-2717	Andrea
89	19-7270	Bob
90	19-6400	Bob
91	19-9864	Bob
92	19-5461	Bob
93	19-2382	Mitch
94	19-6275	Mitch
95	19-4723	Mitch
96	19-1705	Mitch
97	19-1256	Peter
98	19-7926	Mitch
99	19-2021	Bob
100	19-5430	Peter
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"OWNER"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", (tbl)=> Table.AddColumn(tbl,"R",each Number.RandomBetween(1,Table.RowCount(tbl))), type table [CASE FILE NUMBER=text, OWNER=text]}}),
    Twenty = Table.TransformColumns(Group,{{"Data", (tbl)=> Table.FirstN(Table.Sort(Table.Buffer(tbl),{"R"}),Number.Round(Table.RowCount(tbl)*.2,0))}}),
    Expand = Table.ExpandTableColumn(Twenty, "Data", {"CASE FILE NUMBER"}, {"CASE FILE NUMBER"}),
    RC = Table.RemoveColumns(Expand,{"Count"})
in
    RC

 

[JamesCanale]

Office 365?

MrExcelPlayground2.xlsx
	A	B	C	D	E
2	CASE FILE NUMBER	OWNER		20%
3	19-2000	Peter	FALSE	Peter
4	19-2012	Andrea	TRUE	Andrea	19-2012
5	19-1451	Peter	FALSE	Mitch	19-7464
6	19-1234	Mitch	FALSE	Bob	19-1513, 19-2362
7	19-1513	Bob	TRUE
8	19-7464	Mitch	TRUE
9	19-2362	Bob	TRUE
10	19-3561	Bob	FALSE
11	19-1515	Andrea	FALSE
12	19-9285	Peter	FALSE
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=UNIQUE(B3:B12)
E3:E6	E3	=TEXTJOIN(", ",TRUE,IF(($B$3:$B$12=D3)*($C$3:$C$12),$A$3:$A$12,""))
C3:C12	C3	=RAND()<$D$2
Dynamic array formulas.

 

[pasjauo]

Here is a way using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"OWNER"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", (tbl)=> Table.AddColumn(tbl,"R",each Number.RandomBetween(1,Table.RowCount(tbl))), type table [CASE FILE NUMBER=text, OWNER=text]}}),
    Twenty = Table.TransformColumns(Group,{{"Data", (tbl)=> Table.FirstN(Table.Sort(Table.Buffer(tbl),{"R"}),Number.Round(Table.RowCount(tbl)*.2,0))}}),
    Expand = Table.ExpandTableColumn(Twenty, "Data", {"CASE FILE NUMBER"}, {"CASE FILE NUMBER"}),
    RC = Table.RemoveColumns(Expand,{"Count"})
in
    RC

Thanks!

I get a Token EOF error when running this. I'm not that strong in M, so I can't really see whats wrong with the code.

 

[pasjauo]

Office 365?

MrExcelPlayground2.xlsx
	A	B	C	D	E
2	CASE FILE NUMBER	OWNER		20%
3	19-2000	Peter	FALSE	Peter
4	19-2012	Andrea	TRUE	Andrea	19-2012
5	19-1451	Peter	FALSE	Mitch	19-7464
6	19-1234	Mitch	FALSE	Bob	19-1513, 19-2362
7	19-1513	Bob	TRUE
8	19-7464	Mitch	TRUE
9	19-2362	Bob	TRUE
10	19-3561	Bob	FALSE
11	19-1515	Andrea	FALSE
12	19-9285	Peter	FALSE
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=UNIQUE(B3:B12)
E3:E6	E3	=TEXTJOIN(", ",TRUE,IF(($B$3:$B$12=D3)*($C$3:$C$12),$A$3:$A$12,""))
C3:C12	C3	=RAND()<$D$2
Dynamic array formulas.

When doing the first C3 formula, it says TRUE in the whole column - and that makes the other ones right?

EDIT: Well, i'm stupid. I made a typing mistake, and now it works flawlessly!