Forum Discussion
searching groups of items and returning the one with the highest value in each group
how do I search groups of work orders and return the work order number that took the longest to complete. The attached example shows 3 groups of work orders (each group is circled in red). The contractor is graded on the performance of each group of work orders. For example, unit 7707PAT has 8 work orders. All work orders within that group are judged based on the work order than takes the longest to complete (Calculated Hours - column P) two of the work orders took 5 hours, so I would want to somehow isolate or extract either one of those work orders plus the two work orders highlighted below the first two (cells P11 & P19). So in effect, the attachment indicates that there on only three work orders that I want to evaluate (I am only showing 3 out of hundreds of thousands). The contractors bonus is based on what percent of the work orders are completed within a certain number of hours. I have been working on this for weeks and I am stumped. Thanks in advance.
jimmy
- Insert a pivottable using this table as its source.
Drag Unit to the row area
Drag WO# to the row area
Drag Calculated Hours to the bottom-right area
Right-click the Unit column (anywhere within the column), choose Filter, Top 10 items.
In the dialog select top 1 and have it use the calculated hours as the item to do the top 1 on.
2 Replies
- JKPieterseSilver ContributorInsert a pivottable using this table as its source.
Drag Unit to the row area
Drag WO# to the row area
Drag Calculated Hours to the bottom-right area
Right-click the Unit column (anywhere within the column), choose Filter, Top 10 items.
In the dialog select top 1 and have it use the calculated hours as the item to do the top 1 on.- Jstevens64Copper ContributorJan! Perfect! That did the trick!