If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|