Filtering out Duplicate Records while Retaining Most RECENT Entry

**jmt5339** · 12-28-2011, 03:07 PM

Hello all,

Not sure if this is possible to do, but thought I would inquire. I have a large file (about 900 entries) with customers' first and last names and a history of ALL the orders that they have placed with us (Each unique order is an entry in the file). I want to filter the list down so that we have only one entry for the customer: their most RECENT order with us. Is there some function or formula I can use to do this? The only thing I can think to do is eyeball each customer and manually figure out which entry is their most recent order.

**Palmetto** · 12-31-2011, 05:15 PM

For sake of discussion, I will assume customers are identified using a unique ID.

If your data were in the range of A1:D900, and column-A is the customer ID, then possibly this approach may work for you.

Make sure to work with a back up copy of the data.

First, sort the entire data range based on date in ascending order.
In a helper column, say column-E, enter this formula:

=IF(COUNTIF($A$2:A2,A2)<>MAX(COUNTIF($A$2:$A$900,A2)),"",ROW())

Copy the formula down the column.

The formula counts the maximum number of times the unique ID appears in the list and compares it against the current incremental count.
If the current count is less than then maximum occurrences then the formula returns a blank or the row number if current count equals the max.
You could substitute ROW() for anything, like a text string of "most recent".

Apply auto-filter to show blanks and delete the visible rows to retain only the most recent activity.

**jmt5339** · 12-31-2011, 06:13 PM

Thanks, Palmetto. I will try it out at work on Tuesday and let you know if it worked. Greatly appreciated.

**martindwilson** · 12-31-2011, 10:19 PM

assuming id in column a and dates in say col d sort the lot descending on column d
then advanced filter col a on unique values
or do the sort then use the remove duplicates feature in excel 2007 this assumes you date the entries!