Need Help LOOKUP MATCHING

**Hellobuck2013** · 06-05-2013, 05:28 PM

I need help with a Excel Formula ASAP PLEASE I dont want to have to review this document manually. If I have an excel sheet full of data and that in Column A contains (TIN Numbers) by the way there is over 13,000 Line Items/records. Within all of those Lines Items from example in Column A I could have Duplicate TIN Numbers Throughout see below:

Cloumn A
TIN #
123456789
123456789
123456789
789456123
789456123

Column B is a Compleation Review Date for that corresponding TIN from Column A

Column C is a Risk Score (Could be High, Low, etc. that Corresponds back to Column B & A

How can I come up with a formula that looks at Column A TIN Number that might have possible duplication example stated above where there were 3 dups TIN Numbers for 123456789, the formula Looks at those 3 aka duplications and does a Compare and then Pulls Back the Most Recent RISK Completion Date & Score From Column B and C.....?

For all purposes what I am trying to do is get rid of the duplication starting from Duplicate TINS in Column A AND keep all Line items/records based off the most current Risk Score......

So if Column A had

123456789 High Risk (Completion Date 9/12/2012)
123456789 Low Risk (Completion Date 4/9/2009)
123456789 Low Risk (Completion Date 12/12/2007)

For the Whole exel sheet where its like this because of the duplication how can I only keep the Line items that are the most recent TINS, Risk Score, and its based off the Most Recent Completion Date?

PLEASE HELP !!!!

THANK YOU!!

**TMS** · 06-05-2013, 05:43 PM

Sort the data on Date, newest to oldest, within TIN Number, ascending.
Add a Helper column, say, column H
In H2 put the formula: =IF(A2<>A1,1,N(H1)+1) and drag down to the end of the data.
Switch on Autofilter
Filter the Helper column for values >1
Select the visible rows and delete them.

What remains will be the most recent Risk Assessment outcomes.

Regards, TMS

**FDibbins** · 06-05-2013, 05:46 PM

Hi and welcome to the forum

can you perhaps upl;oad a sample workbook, showing what data you have (all sensitive data removed), what your expected outcome is and how you arrived at that?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Hellobuck2013** · 06-05-2013, 06:08 PM

Tmshucks ---- that worked thank you so much

**TMS** · 06-05-2013, 06:28 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.