Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
791 views
in Technique[技术] by (71.8m points)

excel - Get the maximum values of column B per each distinct value of column A

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

enter image description here

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: enter image description here

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...