From duplicate rows only return max value in a different column

**STEFRILEY** · 05-14-2013, 03:05 PM

I have a spreadsheet that lists names in column A. These names do duplicate. In column M I have values of 1, 3, 5, or Failed.
I need a formula that can lookup each name in Column A and only return the highest number in column M. (5,3,1 or Failed)

**oeldere** · 05-14-2013, 03:11 PM

Use pivot table for that.

in the row select name

in the values select number (and choose for max).

**STEFRILEY** · 05-14-2013, 03:40 PM

I created a pivot table and added the names (column A) as the row and made Column M as the values. That did show the results that looked to be a count instead of just the Maximum highest single value. I then changes the values to "Max", but that returns all "0"

Did I do something wrong?

**oeldere** · 05-14-2013, 03:45 PM

I that case you better, add an excel example, without confidentional information.

Then i can take a look and show it to you.

**STEFRILEY** · 05-14-2013, 03:58 PM

Attached is an example. Thanks

**oeldere** · 05-14-2013, 04:12 PM

See the attached file.

The problem was not the pivot table (and the max) => you did the things right.

But the data was tekst.

I changed your formula (see the yellow cells).

Tip: I would not seek in the whole column (e.g. E:E) but determine a range (e.g. E2:E1500).

**Tony Valko** · 05-14-2013, 04:25 PM

Maybe this...

Names in the range A2:A20
Values in the range B2:B20

List of unique names* in the range D2:D6.

This array formula** entered in E2 and copied down:

=IFERROR(1/(1/MAX(IF(A$2:A$20=D2,B$2:B$20))),"Failed")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

* You can easily generate the list of unique names using advanced filter:

http://contextures.com/xladvfilter01.html#FilterUR