Audit, with Random selection from two columns

robbarba

Board Regular
Joined
Apr 17, 2016
Messages
79
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon People of the Forums...

I have a difficult one (well for me)

I am doing a "monthly" Technician Audit, and I have three columns, Incident, Incident Type, and Tech.

I have 2 Incident Types and 27 Techs who completed 1156 Incidents

I need to generate two Incidents per Tech

I thought I could use =INDEX($A$2:$A$1156,LARGE(IF($C$2:$C$1156=E2,ROW($A$2:$A$1156)-ROW(A2)+1),INT(RAND()*COUNTIF($A$2:$A$1156,$E2)+1)))
but that would only give the Tech and not the second field.

Thank you all again!!


2022 01_TechAudit_v3.xlsx
ABCDEFG
1Incident ID*+Incident Type*TechTech
2INC000003552234User Service RequestNelson GilbertBrenda EdgarINC000003693351
3INC000003572352User Service RequestLena EastmanCheryl AdamsUser Service RequestINC000003687655
4INC000003576259User Service RequestGale KinseyCourtney OakleyINC000003689985
5INC000003581404User Service RestorationJulie ChungDoreen HermanINC000003692876
6INC000003593594User Service RequestCourtney OakleyElwood BurnsINC000003687803
7INC000003593770User Service RequestCourtney OakleyFernando BuchananINC000003690144
8INC000003593989User Service RequestLena EastmanGail KeyINC000003690472
9INC000003597609User Service RestorationMarguerite RiversGale KinseyINC000003581404
10INC000003603889User Service RequestJoni McCormickGwen AnthonyINC000003682480
11INC000003606214User Service RequestRickey WashingtonIrene SheaINC000003683887
12INC000003612563User Service RequestGale KinseyJennifer SimonINC000003684653
13INC000003612798User Service RestorationCourtney OakleyJimmie LarsenINC000003678829
14INC000003614969User Service RestorationFernando BuchananJoni McCormickINC000003689233
15INC000003618062User Service RequestMarguerite RiversJulie ChungINC000003687461
16INC000003621350User Service RequestLeon SimonsonLarry ReeseINC000003690467
17INC000003622438User Service RequestJulie ChungLena EastmanINC000003687064
18INC000003622464User Service RestorationElwood BurnsLeon BridgesINC000003689987
19INC000003623076User Service RequestJulie ChungLeon SimonsonINC000003690492
20INC000003623262User Service RestorationLeon SimonsonMarguerite RiversINC000003683916
DATA
Cell Formulas
RangeFormula
G2G2=INDEX($A$2:$A$1156,LARGE(IF($C$2:$C$1156=E2,ROW($A$2:$A$1156)-ROW(A2)+1),INT(RAND()*COUNTIF($A$2:$A$1156,$C2)+1)))
G3:G20G3=INDEX($A$2:$A$1156,LARGE(IF($C$2:$C$1156=E3,ROW($A$2:$A$1156)-ROW(A3)+1),INT(RAND()*COUNTIF($A$2:$A$1156,$C3)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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’)
 
Upvote 0
successfully updated
Thanks for that. (y)

Given 365 version, try this (adjust ranges to suit)

22 02 04.xlsm
ABCDEFGHI
1Incident ID*+Incident Type*TechTechTechIncident 1Incident 2
2INC000003552234User Service RequestNelson GilbertBrenda EdgarNelson GilbertINC000003552234
3INC000003572352User Service RequestLena EastmanCheryl AdamsLena EastmanINC000003593989INC000003572352
4INC000003576259User Service RequestGale KinseyCourtney OakleyGale KinseyINC000003612563INC000003576259
5INC000003581404User Service RestorationJulie ChungDoreen HermanJulie ChungINC000003622438INC000003623076
6INC000003593594User Service RequestCourtney OakleyElwood BurnsCourtney OakleyINC000003593594INC000003593770
7INC000003593770User Service RequestCourtney OakleyFernando BuchananMarguerite RiversINC000003618062INC000003597609
8INC000003593989User Service RequestLena EastmanGail KeyJoni McCormickINC000003603889
9INC000003597609User Service RestorationMarguerite RiversGale KinseyRickey WashingtonINC000003606214
10INC000003603889User Service RequestJoni McCormickGwen AnthonyFernando BuchananINC000003614969
11INC000003606214User Service RequestRickey WashingtonIrene SheaLeon SimonsonINC000003621350INC000003623262
12INC000003612563User Service RequestGale KinseyJennifer SimonElwood BurnsINC000003622464
13INC000003612798User Service RestorationCourtney OakleyJimmie Larsen 
14INC000003614969User Service RestorationFernando BuchananJoni McCormick 
15INC000003618062User Service RequestMarguerite RiversJulie Chung 
16INC000003621350User Service RequestLeon SimonsonLarry Reese 
17INC000003622438User Service RequestJulie ChungLena Eastman 
18INC000003622464User Service RestorationElwood BurnsLeon Bridges 
19INC000003623076User Service RequestJulie ChungLeon Simonson 
20INC000003623262User Service RestorationLeon SimonsonMarguerite Rivers 
Audit (2)
Cell Formulas
RangeFormula
G2:G12G2=UNIQUE(C2:C20)
H2,H8:H10,H12:H20,H11:I11,H3:I7H2=IF(G2="","",TRANSPOSE(INDEX(SORTBY(FILTER(A$2:A$20,C$2:C$20=G2),RANDARRAY(COUNTIF(C2:C20,G2))),SEQUENCE(MIN(2,COUNTIF(C$2:C$20,G2))))))
Dynamic array formulas.
 
Upvote 0
just one last request on this, would it be possible to have the format set like:
Brenda EdgarINC000003667812
Brenda EdgarINC000003659071
Cheryl AdamsINC000003675903
Cheryl AdamsINC000003653576
Courtney OakleyINC000003664118
Courtney OakleyINC000003678028
 
Upvote 0
Will every Tech have at least 2 Incidents, or might some only have one like in my previous example?

If only one is possible, would it be acceptable to report like this ..

1643941778815.png


.. or in that case must Cheryl's name only appear once?
 
Upvote 0
that is correct, some sites are VERY remote and may have only once Incident or they are In office, for project rather than field work
 
Upvote 0
So what is the answer to my question about that?

If only one is possible, would it be acceptable to report like this ..

1643941778815.png


.. or in that case must Cheryl's name only appear once?
 
Upvote 0
If only one is possible, would it be acceptable to report like this ..

.. or in that case must Cheryl's name only appear once?
Let's try to cover either scenario

22 02 04.xlsm
ABCDEFKLM
1Incident ID*+Incident Type*TechTechIncidentTechIncident
2INC000003552234User Service RequestNelson GilbertCourtney OakleyINC000003593770Courtney OakleyINC000003593594
3INC000003572352User Service RequestLena EastmanCourtney OakleyINC000003593594Courtney OakleyINC000003612798
4INC000003576259User Service RequestGale KinseyElwood BurnsINC000003622464Elwood BurnsINC000003622464
5INC000003581404User Service RestorationJulie ChungElwood Burns Fernando BuchananINC000003614969
6INC000003593594User Service RequestCourtney OakleyFernando BuchananINC000003614969Gale KinseyINC000003612563
7INC000003593770User Service RequestCourtney OakleyFernando Buchanan Gale KinseyINC000003576259
8INC000003593989User Service RequestLena EastmanGale KinseyINC000003612563Joni McCormickINC000003603889
9INC000003597609User Service RestorationMarguerite RiversGale KinseyINC000003576259Julie ChungINC000003623076
10INC000003603889User Service RequestJoni McCormickJoni McCormickINC000003603889Julie ChungINC000003581404
11INC000003606214User Service RequestRickey WashingtonJoni McCormick Lena EastmanINC000003593989
12INC000003612563User Service RequestGale KinseyJulie ChungINC000003622438Lena EastmanINC000003572352
13INC000003612798User Service RestorationCourtney OakleyJulie ChungINC000003623076Leon SimonsonINC000003621350
14INC000003614969User Service RestorationFernando BuchananLena EastmanINC000003572352Leon SimonsonINC000003623262
15INC000003618062User Service RequestMarguerite RiversLena EastmanINC000003593989Marguerite RiversINC000003597609
16INC000003621350User Service RequestLeon SimonsonLeon SimonsonINC000003623262Marguerite RiversINC000003618062
17INC000003622438User Service RequestJulie ChungLeon SimonsonINC000003621350Nelson GilbertINC000003552234
18INC000003622464User Service RestorationElwood BurnsMarguerite RiversINC000003597609Rickey WashingtonINC000003606214
19INC000003623076User Service RequestJulie ChungMarguerite RiversINC000003618062 
20INC000003623262User Service RestorationLeon SimonsonNelson GilbertINC000003552234 
21Nelson Gilbert  
22Rickey WashingtonINC000003606214
23Rickey Washington 
24 
25 
26 
Audit (3)
Cell Formulas
RangeFormula
E2:E23E2=LET(u,SORT(UNIQUE(C2:C20)),INDEX(u,INT(SEQUENCE(ROWS(u)*2,,,0.5))))
L2:L18L2=SORT(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,UNIQUE(C2:C20),UNIQUE(FILTER(C2:C20,COUNTIF(C2:C20,C2:C20)>1,"")))&"</c></p>","//c"))
M2:M21M2=IFERROR(INDEX(SORTBY(FILTER(A$2:A$20,(C$2:C$20=L2)*(A$2:A$20<>M1)),RANDARRAY(COUNTIF(C$2:C$20,L2)-(L1=L2))),1),"")
F2:F26F2=IFERROR(INDEX(SORTBY(FILTER(A$2:A$20,(C$2:C$20=E2)*(A$2:A$20<>F1)),RANDARRAY(COUNTIF(C$2:C$20,E2)-(E1=E2))),1),"")
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top