So i got this excel workbook that contains 2 sheets
in sheet 1
column b - employee id
column c - ranking on each equipment
column d - employee name
column e - date
column f - worked hours for employees
column g - shift (day shift or night shift)
column h - equipment id
in sheet 2
column a - date
column b - activity type (working, standby, repair etc.)
column c - equipment type
column d - equipment ID
column f - working hours of the equipment
column g - shift
I want to apply a formula on sheet 2 column E to find the person who might have worked on the corresponding date and equipment and shift. so i want to get employee id's from sheet 1 column B. to do that we have to find the matching date equipment id and shift values from the sheet 1, and find the person who has the best ranking on that equipment. Equipment ranking sheet 1 column c has numbers that goes 1 to 999. 1 is the best rank and higher the number lower the rank it is.
I got this formula which basically finds the matching date, equipment id and shift rows and sorts the filtered data in ascending order and getting the first employee id. but i have a problem that this formula doesnt check it's previous values. One person cannot work on 2 or more equipments on one day. this formula gives me that problem, like one person works on several equipment on one day.
I have tried this formula :
=IFERROR(INDEX(SORT(FILTER(Sheet1!$B$2:$B$213605,(Sheet1!$E$2:$E$213605=Sheet2!A2)*(Sheet1!$H$2:$H$213605=Sheet2!D2)*(Sheet1!$G$2:$G$213605=Sheet2!G2)),1),1,1),"not found")
in sheet 1
column b - employee id
column c - ranking on each equipment
column d - employee name
column e - date
column f - worked hours for employees
column g - shift (day shift or night shift)
column h - equipment id
in sheet 2
column a - date
column b - activity type (working, standby, repair etc.)
column c - equipment type
column d - equipment ID
column f - working hours of the equipment
column g - shift
I want to apply a formula on sheet 2 column E to find the person who might have worked on the corresponding date and equipment and shift. so i want to get employee id's from sheet 1 column B. to do that we have to find the matching date equipment id and shift values from the sheet 1, and find the person who has the best ranking on that equipment. Equipment ranking sheet 1 column c has numbers that goes 1 to 999. 1 is the best rank and higher the number lower the rank it is.
I got this formula which basically finds the matching date, equipment id and shift rows and sorts the filtered data in ascending order and getting the first employee id. but i have a problem that this formula doesnt check it's previous values. One person cannot work on 2 or more equipments on one day. this formula gives me that problem, like one person works on several equipment on one day.
I have tried this formula :
=IFERROR(INDEX(SORT(FILTER(Sheet1!$B$2:$B$213605,(Sheet1!$E$2:$E$213605=Sheet2!A2)*(Sheet1!$H$2:$H$213605=Sheet2!D2)*(Sheet1!$G$2:$G$213605=Sheet2!G2)),1),1,1),"not found")
| book1.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Date | Activity | Eq.Type | Eq.ID | Emp.ID | Total Hour | Shift | ||
| 2 | 4/1/2022 | S1 (Standby - Machine not required) | Other support | #CS01 | not found | 12 | өэ | ||
| 3 | 4/1/2022 | S1 (Standby - Machine not required) | Other support | #CS01 | not found | 12 | шэ | ||
| 4 | 4/1/2022 | S1 (Standby - Machine not required) | Other support | #CS02 | not found | 12 | өэ | ||
| 5 | 4/1/2022 | S1 (Standby - Machine not required) | Other support | #CS02 | not found | 12 | шэ | ||
| 6 | 4/1/2022 | SO (Standby - Other) | Dozer | #DO10 | 4628 | 0.25 | өэ | ||
| 7 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Dozer | #DO10 | 4628 | 9.75 | өэ | ||
| 8 | 4/1/2022 | S1 (Standby - Machine not required) | Dozer | #DO10 | 4628 | 1.75 | өэ | ||
| 9 | 4/1/2022 | F2 (Refueling when machine turned off) | Dozer | #DO10 | 4628 | 0.25 | өэ | ||
| 10 | 4/1/2022 | SO (Standby - Other) | Dozer | #DO6 | 4193 | 0.25 | шэ | ||
| 11 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Dozer | #DO6 | 4193 | 10.25 | шэ | ||
| 12 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Dozer | #DO6 | 4193 | 0.5 | шэ | ||
| 13 | 4/1/2022 | S1 (Standby - Machine not required) | Dozer | #DO6 | 4193 | 1 | шэ | ||
| 14 | 4/1/2022 | SO (Standby - Other) | Drill | #DR04 | not found | 0.75 | өэ | ||
| 15 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Drill | #DR04 | not found | 11.25 | өэ | ||
| 16 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Drill | #DR04 | not found | 9.67 | шэ | ||
| 17 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Drill | #DR04 | not found | 0.5 | шэ | ||
| 18 | 4/1/2022 | F1 (Refueling machine turned on) | Drill | #DR04 | not found | 0.33 | шэ | ||
| 19 | 4/1/2022 | S1 (Standby - Machine not required) | Drill | #DR05 | not found | 12 | өэ | ||
| 20 | 4/1/2022 | S1 (Standby - Machine not required) | Drill | #DR05 | not found | 12 | шэ | ||
| 21 | 4/1/2022 | S1 (Standby - Machine not required) | Drill | #DR07 | not found | 12 | өэ | ||
| 22 | 4/1/2022 | S1 (Standby - Machine not required) | Drill | #DR07 | not found | 12 | шэ | ||
| 23 | 4/1/2022 | SO (Standby - Other) | Drill | #DR08 | not found | 0.5 | өэ | ||
| 24 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Drill | #DR08 | not found | 11.5 | өэ | ||
| 25 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Drill | #DR08 | not found | 8.42 | шэ | ||
| 26 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Drill | #DR08 | not found | 0.83 | шэ | ||
| 27 | 4/1/2022 | W2 (Working inside pit area - cleanup, pulling batters) | Drill | #DR08 | not found | 1 | шэ | ||
| 28 | 4/1/2022 | F1 (Refueling machine turned on) | Drill | #DR08 | not found | 0.58 | шэ | ||
| 29 | 4/1/2022 | DC (Delaying machine turned on/cleaning) | Drill | #DR08 | not found | 0.5 | шэ | ||
| 30 | 4/1/2022 | SO (Standby - Other) | Loader | #EX03 | 5343 | 1.5 | өэ | ||
| 31 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Loader | #EX03 | 5343 | 10.5 | өэ | ||
| 32 | 4/1/2022 | SO (Standby - Other) | Loader | #EX08 | 4138 | 0.58 | шэ | ||
| 33 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Loader | #EX08 | 4138 | 10.92 | шэ | ||
| 34 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Loader | #EX08 | 4138 | 0.5 | шэ | ||
| 35 | 4/1/2022 | SO (Standby - Other) | Loader | #EX09 | 4138 | 0.5 | шэ | ||
| 36 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Loader | #EX09 | 4138 | 11 | шэ | ||
| 37 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Loader | #EX09 | 4138 | 0.5 | шэ | ||
| 38 | 4/1/2022 | SO (Standby - Other) | Truck | #HO26 | 5380 | 0.25 | шэ | ||
| 39 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HO26 | 5380 | 11.25 | шэ | ||
| 40 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HO26 | 5380 | 0.5 | шэ | ||
| 41 | 4/1/2022 | SO (Standby - Other) | Truck | #HO27 | 5411 | 1.67 | өэ | ||
| 42 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HO27 | 5411 | 10.33 | өэ | ||
| 43 | 4/1/2022 | SO (Standby - Other) | Truck | #HO27 | 5380 | 0.5 | шэ | ||
| 44 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HO27 | 5380 | 11 | шэ | ||
| 45 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HO27 | 5380 | 0.5 | шэ | ||
| 46 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HO28 | 5442 | 0.5 | шэ | ||
| 47 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HO28 | 5442 | 6.25 | шэ | ||
| 48 | 4/1/2022 | SO (Standby - Other) | Truck | #HO28 | 5442 | 0.25 | шэ | ||
| 49 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT01 | 5368 | 12 | өэ | ||
| 50 | 4/1/2022 | SO (Standby - Other) | Truck | #HT04 | 5366 | 1.25 | өэ | ||
| 51 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT04 | 5366 | 9.5 | өэ | ||
| 52 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT04 | 5366 | 1.25 | өэ | ||
| 53 | 4/1/2022 | SO (Standby - Other) | Truck | #HT04 | 5380 | 0.25 | шэ | ||
| 54 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT04 | 5380 | 10.25 | шэ | ||
| 55 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT04 | 5380 | 0.5 | шэ | ||
| 56 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT04 | 5380 | 1 | шэ | ||
| 57 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT07 | 5442 | 12 | шэ | ||
| 58 | 4/1/2022 | SO (Standby - Other) | Truck | #HT09 | 5366 | 0.5 | өэ | ||
| 59 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT09 | 5366 | 11 | өэ | ||
| 60 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT09 | 5366 | 0.5 | өэ | ||
| 61 | 4/1/2022 | SO (Standby - Other) | Truck | #HT11 | 5488 | 0.5 | өэ | ||
| 62 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT11 | 5488 | 11 | өэ | ||
| 63 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT11 | 5488 | 0.5 | өэ | ||
| 64 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT11 | 5526 | 12 | шэ | ||
| 65 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT14 | 5434 | 12 | өэ | ||
| 66 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT14 | 5397 | 12 | шэ | ||
| 67 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT16 | 5368 | 12 | өэ | ||
| 68 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT16 | 5526 | 12 | шэ | ||
| 69 | 4/1/2022 | SO (Standby - Other) | Truck | #HT19 | 4424 | 0.5 | өэ | ||
| 70 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT19 | 4424 | 10 | өэ | ||
| 71 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT19 | 4424 | 1.5 | өэ | ||
| 72 | 4/1/2022 | SO (Standby - Other) | Truck | #HT20 | 5380 | 0.5 | шэ | ||
| 73 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT20 | 5380 | 11.5 | шэ | ||
| 74 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT20 | 5380 | 0 | шэ | ||
| 75 | 4/1/2022 | S9 (Standby - Cleaning) | Truck | #HT22 | 5540 | 0.25 | шэ | ||
| 76 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT22 | 5540 | 5.75 | шэ | ||
| 77 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT22 | 5540 | 0.5 | шэ | ||
| 78 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT22 | 5540 | 5.5 | шэ | ||
| 79 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT23 | 5380 | 12 | шэ | ||
| 80 | 4/1/2022 | SO (Standby - Other) | Truck | #HT24 | 5380 | 0.25 | шэ | ||
| 81 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #HT24 | 5380 | 9.25 | шэ | ||
| 82 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #HT24 | 5380 | 0.5 | шэ | ||
| 83 | 4/1/2022 | S1 (Standby - Machine not required) | Truck | #HT24 | 5380 | 2 | шэ | ||
| 84 | 4/1/2022 | SO (Standby - Other) | Truck | #VO4 | 5380 | 0.5 | шэ | ||
| 85 | 4/1/2022 | W1 (Working inside pit area - loading trucks) | Truck | #VO4 | 5380 | 11 | шэ | ||
| 86 | 4/1/2022 | S7 (Standby - Breaks and Rest/including lunch) | Truck | #VO4 | 5380 | 0.5 | шэ | ||
Sheet2 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E86 | E2 | =IFERROR(INDEX(SORT(FILTER(Sheet1!$B$2:$B$213605,(Sheet1!$E$2:$E$213605=Sheet2!A2)*(Sheet1!$H$2:$H$213605=Sheet2!D2)*(Sheet1!$G$2:$G$213605=Sheet2!G2)),1),1,1),"not found") |
