Find the last version of a contract record, if there are multiple contract versions?
MS Excel v 2007
Windows XP Professional
novice user with Excel
I have an Excel file that has 10K contract records. Each contract will have a name, a contract version, contract effective date, contract expiration date. For the purpose of reporting I only want the last version of any contract.
How do I find the last version of a contract record, if there are multiple contract versions?
In the example below I only want to consider (2) of the possible (5) records and they are:
Contract1 version 03
Contract2 version 02
Contract Name Version effective date expire date
Contract1 01 01/01/2007 12/31/2007
Contract1 02 01/01/2008 12/31/2010
Contract1 03 01/01/2010 06/15/2011
Contract2 01 01/01/2009 12/31/2009
Contract2 02 01/01/2010 12/31/2013
Thanks in advance
Misty
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Patrick ........ beautiful! This did exactly what I needed and has saved me much additional manual work.
I selected your solution because a) I cannot always count on the data to be sorted and b) it is more useful for me to have a physical data file that is not making a reference to another field.
Many thanks for the assistance.
Misty
I selected your solution because a) I cannot always count on the data to be sorted and b) it is more useful for me to have a physical data file that is not making a reference to another field.
Many thanks for the assistance.
Misty
You're most welcome, Misty
A simple vlookup formula should be enough as it sould return the last valid result.
Check the attached file for your solution.
Book1.xlsx