Get the maximum values of column B per each distin

Question

I have an Excel spreadsheet of the form:

A,B
X,1
X,5
Y,4
Y,11
X,7
Z,1

I would like to get the maximum value of column B for each distinct value of column A - how can I do it via pure Excel formula?

Desired result (order is irrelevant):

A,B
Y,11
X,7
Z,1

In other words, I would like Excel's version of an SQL query

SELECT   A,max(B)
FROM     myTable
GROUP BY A

Any version of Excel is acceptable, but I use 365 one.

Pivot tables are an acceptable approach (I currently did it with a pivot myself) but I would strongly prefer a real formula - the main goal of the question is to enhance my understanding of formula programming in Excel. No VBA

Answer 1

With data in columns A and B use the Array Formula:

=MAX(IF(A1:A6="x",B1:B6))

Same for "y" and "z"

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Notice the braces in the Formula Bar

EDIT#1:

To generate the formulas automatically, first copy column A to column C.

Then use the Remove Duplicate feature in the Data tab:

Then enter the Array Formula in cell D1:

=MAX(IF(A$1:A$14=C1,B$1:B$14))

and copy down:

The formula is only entered once and then copied down!

Answer 2

Gary's Student's answer is correct. I added a couple things.

Also, a link to the method: http://blog.contextures.com/archives/2011/07/27/finding-min-if-or-max-if-in-excel/

A distinct list of values can be generated in a couple ways, here's one:

And here's links to a method or two for distinct lists. All with array formulas:

Ignore Duplicates and Create New List of Unique Values in Excel

Getting unique values in Excel by using formulas only

Answer 3

Try below. Note I moved your desired output table to Columns D and E. You will not need to hard-code any values into formulas.

Data

D   E
Y   11
X   7
Z   1

Formulas

E2 Formula: ={MAX(--($D1=A1:A6)*B1:B6)}
E3 Formula: ={MAX(--($D2=A2:A7)*B2:B7)}
E4 Formula: ={MAX(--($D3=A3:A8)*B3:B8)}

Answer 4

Pivot table is the correct answer.

1. Select column A's title and drag it to the Row Labels section.
2. Select column B's title and drag it to the Values section.
3. Click the arrow on column B's title in the values section and choose Value Field Settings.
4. Select Max