Sort highest match in excel

"Update

Here's a bit of magic using an array formula. This type of formula require you to press Ctrl+Shift+Enter, when entering the formula into a cell (notice the {}-brackets surrounding the formula in the screenshot when doing this). In other words - type in the formula, and the press Ctrl+Shift+Enter instead of just Enter.

Use the following formula in column C to mark the highest dates within a group in column A:

=IF($B2=MAX(IF($A$2:$A$100=$A2;$B$2:$B$100; 0));""HIGHEST"";"""")

The result being:

Note that the formula only handles the first 100 rows. You can change this easily if needed, though. E.g.:

=IF($B2=MAX(IF($A$2:$A$65000=$A2;$B$2:$B$65000; 0));""HIGHEST"";"""")

This formula will handle the first 65.000 rows.

Download example file here (download link valid until aug 24 2009).

Original answer

You could put the following formula in the C column:

=IF($B2=MAX($B:$B);""HIGHEST"";"""")

The result being:"