Excel Pivot Tables remainder of the row

[rnealon]

If I have the following Pivot Table source data:

Location Date units A B
DC 25-Jan 10 11 A
DC 26-Jan 15 12 B
Chicago 25-Jan 12 14 C
Chicago 26-Jan 13 15 D
LA 25-Jan 17 19 E
LA 26-Jan 15 20 F

I'm trying to get a pivot table with the following info:

Max of units Date A B
DC 15 26-Jan 12 B
Chicago 13 26-Jan 15 D
LA 17 25-Jan 19 E

I'm trying to find the complete row of data when the maximum units is
selected for each location.
I can find the maximum, just fine. I'm having a hard time getting the
all the other associated with that row.

Is there a way to do this in pivot tables?
Bob Nealon

 

[Debra Dalgleish]

A pivot table isn't designed to pull individual records from a source
table. You could use the pivot table to find the maximum units per
location, then use Vlookup or index/match formulas outside the pivot
table, to pull the data from the source.

However, if you had ties for maximum units, only one record would be
returned.