Return Latest Date from a range of data

**Kenji** · 04-01-2010, 04:57 AM

Dear Admin,

I got a problem that I always need to do a donkey work that to sort out the latest date from my supplier list, For Example:

Supplier Date
123 1/1/10
125 1/1/10
126 1/1/10
123 2/1/10
125 2/1/10
126 2/1/10

The result should be return :

Supplier Date
123 2/1/10
125 2/1/10
126 2/1/10

With this I no need to check day by day , date by date, do sorting and count manually for thosand lines.
Is it possible to work around a coding to perform this action ? or using some excel formula ?? Thanks and Regards

**sweep** · 04-01-2010, 05:05 AM

Hi,

You could use data > sort

to order the data in date order, which would show you the latest date, then

data > filter

to filter only the latest date?

**Kenji** · 04-01-2010, 05:32 AM

Dear Sweep,

I sort already, problem is I got thoudsand suppliers, My 1st sort is all the same supplier number, second sort by date, I cant get the result as I mentioned above but I got thoudsand of different suppliers, I need to pefrom the donkey work to count and do the report myself @@ , Regards

**DonkeyOte** · 04-01-2010, 05:34 AM

Why not use a Pivot Table ?

Set Supplier as Row Field, Date as Data Field set to MAX and format as Date.
(use a Dynamic Named Range as source for PT)

Originally Posted by Kenji

I sort already, problem is I got thoudsand suppliers, My 1st sort is all the same supplier number, second sort by date

Your sample does not match the above ... if the above is true then the last date can be returned by conducting a basic VLOOKUP, ie: =VLOOKUP(suppliercode,A:B,2)