Extract Oldest Expiraton Dates

[drainedge]

I'm trying to find the oldest Contract expiration dates in the attached sample file.

Column A is the company name. The sample company is listed multiple times because it has unique "contracts"
Column B is an account #.
Column C is description.
Column D is status.
Column E is a contract number. The same company can and will have multiple contracts
Column F is the "Contract Expiration Date". These expiration dates are different even though the same company provides the service. These dates change monthly.

Could someone help with a formula, that would pull the oldest contract expiration date (entire row) and place it in another worksheet or highlight it? Basically there are 16 rows but I only need the 5 rows of data that have a highlighted date in Column F.

Typically, I'll have 1100 rows each month that I would have to do this with. Any help would be great.

Thanks

[QEJohn]

Looking at your sample sheet, i'm not sure what you mean by the oldest dates. The cells you have highlighted are the 1st, 2nd, 4th, 6th and 7th dates that are furthest away from today. Please clarify your request.

Otherwise, would sorting work?

[Pete_UK]

It looks like you are looking for the largest date (not the "oldest" !!) - you can use the LARGE function for this.

Hope this helps.

Pete

[drainedge]

QEJohn, yes, the largest dates. Sorry about that. How can I pull those rows only? If there are multiple largest dates, the first one is fine.

[Pete_UK]

In the attached file I have inserted a new sheet with the necessary formulae in to give you the top-5 latest expiry dates. You can just copy the formulae down further if you need the top-10, or top-20 etc.

In use, you can just copy new data into the account listing sheet, then the top-5 will automatically adjust.

Hope this helps.

Pete

[drainedge]

No, that's not what I need. See below. There are about 1100 rows. 600 are duplicate company names like "T Company" and 500 are unique non-duplicated names like "Car Sales Inc". For all the duplicate names, I need the date that is furthest into the future out of the other rows. I have the rows in this example bolded. Any help would be great. Thanks

Company Acct # Descrip. Status Contract # Expiration Date

T COMPANY 542304ac adfsadfs Active 44-4 2/28/2014

T COMPANY 542304ac asdfsad Active 44-5 12/31/2016

T COMPANY 542304ac dsafsadf Active 44-6 12/31/2012

T COMPANY 542304ac asdfadsf Active 44-7 12/31/2012

T COMPANY 542304ac asdfdsaf Active 44-8 12/31/2013

T COMPANY 542304ac dsafds Active 44-9 12/31/2012

T COMPANY 542304ac asdfsdf Active 44-10 12/31/2012

T COMPANY 542304ac adsfasd Active 44-11 12/31/2015

T COMPANY 542304ac adfasasdafds Active 44-12 12/31/2012

CAR SALES INC 542304ad adfadsfa Active 44-13 3/31/2014

DIAG CO. 542304ae adfsadfs Active 44-14 4/30/2016

DIAG CO. 542304ae afdsf Active 44-15 9/30/2015

BOTT HAUL 542304af asdfadsf Active 44-16 6/30/2015

BOTT HAUL 542304af adsfasd Active 44-17 1/31/2014

[Pete_UK]

You didn't say that you wanted the latest date for each unique name, which is what I think you are saying now.

The attached shows how you can do this. I've copied the formulae down to row 12 - copy further if needed.

Note that the two formulae in columns A and B are array formulae, so need to be committed with the key combination of CTRL-SHIFT-ENTER.

Hope this helps.

Pete