Find max or min values from a list of duplicates in Excel

In Excel sometimes you will need to find either the first or last value from a mixed list of values that also include duplicates.

Later versions of Excel feature a Remove Duplicates button under the Data tab in the ribbon.  This only removes unique duplicates, if you start including your date or sales values in the list you find Excel sees only unique values.

The work around is to use an Excel Array Formula.

You can combing MIN / MAX and IF to lookup the value from an array and then grab the relevant version of the data.

Below I use MAX to find the latest date for each of the unique values.

Syntax

=MAX(IF(<value to lookup in list> = <unique value>,<range of date for all values>))

To create this as an Array Function press Ctrl + Shift + Enter rather than just Enter

You can tell an Array Function by the curly brackets around the entire request (including equals)

{=MAX(IF(A:A=D2,B:B))}

Example

Excel Max List Array Function

You can then fill down appropriately to populate the rest of your values.

If using dates you will need to format the result cell as a date field or Excel will display the numeric value for the date instead.

This entry was posted in Excel, Microsoft. Bookmark the permalink.