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.
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.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Thanks, Palmetto. I will try it out at work on Tuesday and let you know if it worked. Greatly appreciated.
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!
Last edited by martindwilson; 12-31-2011 at 09:22 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks