Excelfilter: Get max from selection of items based on another column?

July 26, 2015 10:10 AM

I have an Excel question. I have a list of items, some of which are duplicates. Each item has a figure attached. I'd like to find the maximum value for each duplicate item and put that in another cell.

So basically what I have is data that look like this:

Category 1 | 5
Category 1 | 7
Category 1 | 13
Category 1 | 1
Category 2 | 4
Category 3 | 6
Category 3 | 1

And so on.

I want to find the maximum value in column 2 for each category in Column 1, so the output from the above list would be:

Category 1 | 13
Category 2 | 4
Category 3 | 6

How can I do this? I have gotten as far as getting the list of categories with duplicates removed to appear on my second sheet, but I can't get it to find the maximums for each category.

Thanks!

posted by synecdoche to Computers & Internet (5 answers total)

Pivot tables should do this. Instead of sum or count, just choose maximum.
posted by jeather at 10:17 AM on July 26, 2015 [2 favorites]

This looks potentially useful.
posted by jon1270 at 10:19 AM on July 26, 2015

I came here to say pivot tables as well. Grouping data by column A and then doing something (adding, finding the max, min, average, etc) of the matching values in column B is what pivot tables excel at.
posted by nalyd at 10:35 AM on July 26, 2015

Definitely do this as a pivot table, not with a formula - it's more efficient, more accurate, and prettier. Exactly how to create the pivot table varies between versions of Excel (if you can be more specific we can walk you through it), but in general you're going to want your Category in the "rows", your figure in the "Values", and then you'll want to find in the Value Field Settings (possibly by right clicking or clicking a down arrow) a way to "Summarize value field by" and select "max".
posted by brainmouse at 10:50 AM on July 26, 2015 [1 favorite]

Suppose your two columns are in A and B. You can get the max of Category 1 with an array formula (use shift+enter instead of enter).

{=max((A1:A7="Category 1")*B1:B7)}

(I'm doing this from memory, without Excel in front of me, so apologies if this doesn't quite work. It definitely can be made to work.)
posted by paper chromatographologist at 3:27 PM on July 26, 2015