Sorting to get the latest of a kind

[varkeychaya]

Hi

I want to sort a table which contains the names of customers, dates and transactions, such that when filtered it will contain only the latest transaction of each customer. Customers should not be repeated.

any solutions???

[Marcelo]

hi,

try to use a Pivot table

hth
regards from Brazil
Marcelo

"varkeychaya" escreveu:

>
> Hi
>
> I want to sort a table which contains the names of customers, dates and
> transactions, such that when filtered it will contain only the latest
> transaction of each customer. Customers should not be repeated.
>
> any solutions???
>
>
> --
> varkeychaya
> ------------------------------------------------------------------------
> varkeychaya's Profile: http://www.excelforum.com/member.php...o&userid=36590
> View this thread: http://www.excelforum.com/showthread...hreadid=563321
>
>

[Duke Carey]

This describes a 3 step process. There may be simpler ways to do this

The three steps are:
1) ID the unique customer names/IDs
2) find the max date for each customer
3) extract the corresponding rows

Assume your data consists of three columns labeled Cust, Date, and Order, in
columns A, B, and C, respectively.

Step 1
Use Data>Filter>Advanced filter to find the unique customers. Set the list
range to all of column A where your Customers are - include the column
heading. Click on 'Copy to another location' and 'Unique records only' In
the Copy to edit box type F1, then click on OK.

You should see the unique customers in column F.

Step 2 - Max Dates
Type "Date" or whatever the heading for the date column is in cell G1. In
G2 use this array formula:

=MAX(IF($A$2:$A$10=F2,$B$2:$B$10))

but adjust the A$10 and B$10 to reflect the last row of your data. Be sure
to enter this formula by pressing Ctrl-Shift-Enter. Now you have the max
date for each customer

Step 3 - Extract the data
Again use Data>Filter>Advanced filter, but be sure to include all of your
original data, not just column A. Again - Copy to a new location, and
indicate J1 as the location. In the Criteria range edit box indicate all of
the populated cells in columns F&G where you have the customers and max
dates. Click on OK and you should get the records you want


"varkeychaya" wrote:

>
> Hi
>
> I want to sort a table which contains the names of customers, dates and
> transactions, such that when filtered it will contain only the latest
> transaction of each customer. Customers should not be repeated.
>
> any solutions???
>
>
> --
> varkeychaya
> ------------------------------------------------------------------------
> varkeychaya's Profile: http://www.excelforum.com/member.php...o&userid=36590
> View this thread: http://www.excelforum.com/showthread...hreadid=563321
>
>