Geek Answers Handbook

Get the maximum values ​​of column B for each individual value of column A

I have an Excel spreadsheet 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 individual value of column A - how can I do this with a clean Excel formula ?

Desired result (order doesn't matter):

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

In other words, I would like the Excel version of the SQL query

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

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

Pivot tables are an acceptable approach (I am currently doing this using a pivot table), but I would prefer the real formula . The main goal of the question is to improve understanding of formula programming in Excel. No vba

+4
source share
4

A B :

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

"y" "z"

Ctrl + Shift + Enter, Enter.

enter image description here

EDIT # 1:

, A C.

" " "":

enter image description here

D1:

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

:

enter image description here

!

+2

Pivot table is the correct answer.

  • Select the Header column and drag it to the Row Labels section.
  • Select the column heading for column B and drag it to the Values ​​section.
  • Click the arrow in the column heading of column B in the value section, and select Field Field Options.
  • Choose Max
+1
source

Try below. Note. I have moved your desired output table to columns D and E. You do not need to hardcode any values ​​in the 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)}
+1
source

More articles:


All Articles