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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…