Hi everyone, I am struggling with a solution. Our production report lists the top 9 downtime events. this list comes from Sheet4 which contains a bunch of formulas..... I hope this makes sense.
the data on sheet4 is in range C1:I37, and it looks like below:
the full table looks like this
On sheet named DMS i want to display the top 9 events from sheet4 but highest to lowest. The problem i have is that there could be multiple events with the same amount of minutes of lost time, so doing a lookup just finds the first of the duplicates all the time.
Is there a formula that i can use to combine LARGE but somehow also ensures it matches the data in the columns to left and right??
sorry, poor way to explain that.
the data on sheet4 is in range C1:I37, and it looks like below:
| Shift Report & DMS Dashboard - 231120.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | |||
| 1 | LINE | ISSUE | MINUTES LOST | ROOT CASUE | SHIFT | ||||
| 2 | HART | Smash at Transfer Bridge | 4 | Packaging | |||||
| 3 | MICRO | Tubs Falling off Accumulator | 20 | DAY | |||||
| 4 | MICRO | Filler Outfeed Jam | 12 | Tubs Sliding | DAY | ||||
| 5 | BOSCH | Smash in Casepacker | 8 | Loading Plate | |||||
| 6 | BOSCH | Cheese on Belt | 9 | Bottom Seal | DAY | ||||
Sheet4 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:G6,C2:D6 | C2 | =IF(Day!A32="","",Day!A32) |
| I2:I6 | I2 | =IF(A2>0,"DAY","") |
the full table looks like this
| Shift Report & DMS Dashboard - 231120.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | |||
| 1 | LINE | ISSUE | MINUTES LOST | ROOT CASUE | SHIFT | ||||
| 2 | HART | Smash at Transfer Bridge | 4 | Packaging | |||||
| 3 | MICRO | Tubs Falling off Accumulator | 20 | DAY | |||||
| 4 | MICRO | Filler Outfeed Jam | 12 | Tubs Sliding | DAY | ||||
| 5 | BOSCH | Smash in Casepacker | 8 | Loading Plate | |||||
| 6 | BOSCH | Cheese on Belt | 9 | Bottom Seal | DAY | ||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 | |||||||||
| 14 | HART | Doboy Forming | 3 | Packaging | |||||
| 15 | MICRO | Foil Placement | 6 | Foils not releasing | |||||
| 16 | MICRO | WOP | 14 | Runny Product - Let Sit | ARVO | ||||
| 17 | BOSCH | Smash at Casepacker layerplate | 12 | ARVO | |||||
| 18 | BOSCH | Cheese on Belt | 16 | Bottom Seal | ARVO | ||||
| 19 | |||||||||
| 20 | |||||||||
| 21 | |||||||||
| 22 | |||||||||
| 23 | |||||||||
| 24 | |||||||||
| 25 | |||||||||
| 26 | HART | Chiller | 60 | Water on packets from wash | NIGHT | ||||
| 27 | HART | Xray | 13 | Belt running off | NIGHT | ||||
| 28 | MICRO | Tub Dispenser | 2 | Distorted 150g Chilli Tubs | |||||
| 29 | MICRO | Outfeed Jam | 2 | Tubs Jamming | |||||
| 30 | BOSCH | Cheese on Belt | 9 | Runny Product | NIGHT | ||||
| 31 | BOSCH | Metal Detector | 17 | Stopping all the time, ESN done. | NIGHT | ||||
| 32 | |||||||||
| 33 | |||||||||
| 34 | |||||||||
| 35 | |||||||||
| 36 | |||||||||
| 37 | |||||||||
Sheet4 | |||||||||
On sheet named DMS i want to display the top 9 events from sheet4 but highest to lowest. The problem i have is that there could be multiple events with the same amount of minutes of lost time, so doing a lookup just finds the first of the duplicates all the time.
Is there a formula that i can use to combine LARGE but somehow also ensures it matches the data in the columns to left and right??
sorry, poor way to explain that.
