Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting to get the latest of a kind
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting to get the latest of a kind
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting to get the latest of a kind
This describes a 3 step process. There may be simpler ways to do this
The three steps a 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 DataFilterAdvanced 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 DataFilterAdvanced 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Sorting Sorting | Excel Discussion (Misc queries) | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) |