Macro to get the highest value in a table

Closed
francis - Aug 3, 2016 at 02:41 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 4, 2016 at 11:32 AM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 4, 2016 at 11:32 AM
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
0