Grouping by max value of a field in relation to another field

[#1]

Hi,

I have a sheet that's essentially laid out as follows:

[JobCode]     [WorkCenter]     [Run Number]     [Time]     [Qty]

1                  abc                   1                       .27          20

1                  abc                   2                       .27          20

1                  def                    3                       .27          20

1                  def                    4                       .27          20

2                  abc                   1                       .52          35

2                  abc                   2                       .52          35

2                  def                    3                       .52          35

2                  def                    4                       .52          35

2                  ghi                    5                       .52          35

2                  ghi                    6                       .52          35

I'm trying to figure out how to remove the duplicate values by only taking the maximum Run Number for each Job Code. So it should look something like this:

[JobCode]     [WorkCenter]     [Run Number]     [Time]     [Qty]

1                  def                    4                       .27          20

2                  ghi                    6                       .52          35

It should also be noted that the Time and Qty fields are already the sum of all the run numbers' time and quantity values in each job code, but they're still duplicating because we're displaying every run number instead of just the max.

Any help would be greatly appreciated. Thank you!

[benvatvandata]

Hi Antonio,

Thanks for the reply - your solution works for the example, however using DISTINCT in the Sum will be an issue for me because that expression is summing all of the JobStepNum values (there are multiple JobStepNums for each JobCode, WorkCenter, etc.), and there are multiple JobStepNums within each job that may have the same qty... so I would still need to sum values that may be the same... Does that make sense?

Thanks,

Ben