CODEWITHSUNDEEP
vbaexcelexcel-formula
barker
barker

Reputation: 1055

Select max of each group with its associated values

I have a large table formatted like so: 

+-----+-------+-------+
| id  | count | group |
+-----+-------+-------+
| 123 | 37    | a     |
+-----+-------+-------+
| 121 | 26    | a     |
+-----+-------+-------+
| 442 | 33    | a     |
+-----+-------+-------+
| 923 | 55    | b     |
+-----+-------+-------+
| 783 | 12    | b     |
+-----+-------+-------+

My goal output would select the max count as well as the id associated with the max count for each group. Is there an array function that can do this? This array function gets me the max count, but it's not always distinct so i'm unsure how to grab the id as well: 

{=MAX(IF(C:C=C1,B:B))}

So this would check the group in the 3rd (C) column and return the max count in the 2nd (B) column, but how can i grab the first (A) column as well?

Upvotes: 2

Views: 10790

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Perhaps this is what you want. In the example below

F2:  =MAXIFS(count,group,E2)
G2: =INDEX($A:$A,MAX((group=E2)*(count=F2)*ROW(id)))

and the formula in G2 is an array formula which must be confirmed by holding down ctrl + shift while hitting Enter

Of course, if you don't have MAXIFS in your version of Excel, and you don't like to use the ctrl + shift + Enter sequence, you can use these formulas instead:

F2: =AGGREGATE(14,4,(group=E2)*count,1)
G2: =INDEX($A:$A,AGGREGATE(14,4,(count=F2)*(group=E2)*ROW(id),1))

enter image description here

Upvotes: 3

Related Questions