Macro to get the highest value in a table

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
A B C
10 1 apple
10 1 banana
10 1 orange
10 2 apple
10 2 banana
10 2 orange
12 1 apple
12 1 banana
12 1 orange
12 2 apple
12 2 banana
12 2 orange
12 3 apple
12 3 banana
12 3 orange

basically want a macro that will only get the result below based on the above data
10 2 apple
10 2 banana
10 2 orange
12 3 apple
12 3 banana
12 3 orange
which is basically getting the highest value in column B.

Thank you in advance.

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Francis,

Put column A and C next to each other, then use Advanced Filter (found under the data tab) to filter on unique values and place in a different location:


Then use the following formula in colmn G:
=MAX(($A$2:$A$16=E2)*($C$2:$C$16) *($B$2:$B$16=F2))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter.

Best regards,
Trowa