Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Finding most recent date for each customer - XL 2007

I have a simple list, the left column is the customer number, the right
column is the date of every sale made to the customer. I need to filter this
list to show the most recent date only for each customer. The list has many
hundreds of customer numbers with between 1 and 200 dates per customer.
There are no blanks in the list

eg.
1234 25/10/2009
1234 26/10/2009
1234 03/03/2010
1356 23/12/2009
1356 25/04/2010
1999 06/02/2009
2157 09/05/2009
2157 10/06/2009
2157 25/11/2009
2157 23/04/2010

result would be
1234 03/03/2010
1356 25/04/2010
1999 06/02/2009
2157 23/04/2010
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Finding most recent date for each customer - XL 2007

Hi,

You could try this

Suppose your data is in range A1:B500 (where row 1 is the header row). Go
to Data Filter Advanced Filter and select Copy to another location. In
the list box, select A1:B500. Leave the criteria box blank and in the copy
to box, select any blank cell. Check the box for unique records. This will
get you all the unique customers

Suppose the first unique customer is in cell D2. In cell E2, enter the
following array formula (Ctrl+Shift+Enter) and copy down

=max(if(($A$2:$A$500=E2),$B$2:$B$500))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Geoff B" wrote in message
...
I have a simple list, the left column is the customer number, the right
column is the date of every sale made to the customer. I need to filter
this
list to show the most recent date only for each customer. The list has
many
hundreds of customer numbers with between 1 and 200 dates per customer.
There are no blanks in the list

eg.
1234 25/10/2009
1234 26/10/2009
1234 03/03/2010
1356 23/12/2009
1356 25/04/2010
1999 06/02/2009
2157 09/05/2009
2157 10/06/2009
2157 25/11/2009
2157 23/04/2010

result would be
1234 03/03/2010
1356 25/04/2010
1999 06/02/2009
2157 23/04/2010


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default Finding most recent date for each customer - XL 2007

Excel 2007 PivotTable
Consolidate and filter by most recent date.
No formulas needed.
http://c0718892.cdn.cloudfiles.racks.../04_30_10.xlsx
Pdf preview:
http://www.mediafire.com/file/yo4dqi5cnzj/04_30_10.pdf
  #4   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by Herbert Seidenberg View Post
Excel 2007 PivotTable
Consolidate and filter by most recent date.
No formulas needed.
http://c0718892.cdn.cloudfiles.racks.../04_30_10.xlsx
Pdf preview:
http://www.mediafire.com/file/yo4dqi5cnzj/04_30_10.pdf
Hi Herbert, in my case the pivot table and the max filter doesn't seem to work. As the previous post, I have several entries for the same customer but I also have a status ID for each entry. I need to filter the most recent date by customer and obtain the status ID of that most recent date.
Example:
CompanyID Date Status ID
2540 19/10/12 1
2540 06/11/12 38
2539 19/10/12 1
2539 06/11/12 39
2538 19/10/12 1
2538 05/11/12 1
2538 13/11/12 37
2537 19/10/12 1
2537 06/11/12 23
2536 19/10/12 1
2536 06/11/12 22

The result I'm searching for is:
CompanyID Date Status ID
2540 06/11/12 38
2539 06/11/12 39
2538 13/11/12 37
2537 06/11/12 23
2536 06/11/12 22

I've attached a sample of my list.

Many thanks in advance
Attached Files
File Type: txt BD Call Situation - 211112-3.txt (6.4 KB, 83 views)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding the most recent date -- Excel 2K tech1NJ Excel Worksheet Functions 1 October 29th 08 02:57 AM
Finding the most recent date - Excel 2K tech1NJ Excel Worksheet Functions 3 October 28th 08 07:57 PM
Finding most recent date by customer number Matt Beardsley Excel Worksheet Functions 2 September 8th 06 05:31 AM
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET brunod Excel Discussion (Misc queries) 1 July 7th 06 07:01 PM
Finding the most recent month's (or whatever) data Dallman Ross Excel Discussion (Misc queries) 10 July 1st 06 09:19 PM


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"