Prev: Adding fill to cells only when value is "No" in another cell
Next: Dynamic sheet reference in Name formula
From: Geoff B on 29 Apr 2010 20:58
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
From: Ashish Mathur on 29 Apr 2010 21:08
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" <GeoffB(a)discussions.microsoft.com> wrote in message
news:C8E55EF3-3DA8-4A42-9894-7E8E25149ECC(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Adding fill to cells only when value is "No" in another cell
Next: Dynamic sheet reference in Name formula