Get maximum value based on unique values

Question

I have two columns in Excel as follows:

col1  col2

1     10
1     22
1     11
1     23
1     14
2     16
2     12
2     10
2     9

How do I write a command to returns the maximum value from col2 corresponding to each unique value in col1?

So here I need to write a command to get 23 and 16.

The data I have shown here are dummies; I have 600 unique values in col1 in my actual data set.

Answer 1

Since the left hand values seem to be in order, Subtotal should work.

Answer 2

Even if a solution has been found. I think it is too complicated and oversized to use the pivot for this and it might not be possible in all scenarios. Here is one that can be found searching the internet for "max if excel" (e.g. see here; adapted)

{=MAX(IF(A:A=A1,B:B))}

Attention! This is an array formula, you have to enter it without the curly brackets and then press Ctrl+Shift+Enter to submit the formula.

Answer 3

Pivot tables: the single most underutilized and powerful feature of excel.

• File menu:
• Insert
• Pivot Table
• Select range
• Fill in as indicated in image below

Step by Step:

• Place cursor in cell to right of all data on sheet.
• Select insert from menu
• select pivot table icon
• select pivot table from pop-up of icon
• use the button to select the range of cells included (all 600+ rows in 2 columns) (or just enter $A:$B)
• select ok
• A pivot table field list appears on the right:
• Drag and drop row 1 into row labels.
• drag and drop row 2 into values area.
• left click on "... of Row 2"
• select value field settings
• select "Max" instead of count or sum
• select ok and you should have desired results.

The pivot table will find the max value in row 2 for each unique value in row 1 which is what I believe you are after.