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!!
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 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Incident ID*+ | Incident Type* | Tech | Tech | |||||
2 | INC000003552234 | User Service Request | Nelson Gilbert | Brenda Edgar | INC000003693351 | ||||
3 | INC000003572352 | User Service Request | Lena Eastman | Cheryl Adams | User Service Request | INC000003687655 | |||
4 | INC000003576259 | User Service Request | Gale Kinsey | Courtney Oakley | INC000003689985 | ||||
5 | INC000003581404 | User Service Restoration | Julie Chung | Doreen Herman | INC000003692876 | ||||
6 | INC000003593594 | User Service Request | Courtney Oakley | Elwood Burns | INC000003687803 | ||||
7 | INC000003593770 | User Service Request | Courtney Oakley | Fernando Buchanan | INC000003690144 | ||||
8 | INC000003593989 | User Service Request | Lena Eastman | Gail Key | INC000003690472 | ||||
9 | INC000003597609 | User Service Restoration | Marguerite Rivers | Gale Kinsey | INC000003581404 | ||||
10 | INC000003603889 | User Service Request | Joni McCormick | Gwen Anthony | INC000003682480 | ||||
11 | INC000003606214 | User Service Request | Rickey Washington | Irene Shea | INC000003683887 | ||||
12 | INC000003612563 | User Service Request | Gale Kinsey | Jennifer Simon | INC000003684653 | ||||
13 | INC000003612798 | User Service Restoration | Courtney Oakley | Jimmie Larsen | INC000003678829 | ||||
14 | INC000003614969 | User Service Restoration | Fernando Buchanan | Joni McCormick | INC000003689233 | ||||
15 | INC000003618062 | User Service Request | Marguerite Rivers | Julie Chung | INC000003687461 | ||||
16 | INC000003621350 | User Service Request | Leon Simonson | Larry Reese | INC000003690467 | ||||
17 | INC000003622438 | User Service Request | Julie Chung | Lena Eastman | INC000003687064 | ||||
18 | INC000003622464 | User Service Restoration | Elwood Burns | Leon Bridges | INC000003689987 | ||||
19 | INC000003623076 | User Service Request | Julie Chung | Leon Simonson | INC000003690492 | ||||
20 | INC000003623262 | User Service Restoration | Leon Simonson | Marguerite Rivers | INC000003683916 | ||||
DATA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2 | G2 | =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:G20 | G3 | =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. |