filtering records with their latest /max version values only


I have multiple purchase order numbers and change order version numbers in column A and their adjusted po values in column b

60017                     120            
60017 v.1                150
60017 v.2                200
60017 v.3                250
67001 v.1                  45
67001 v.2                   76

how do I build a macro that filters only the latest po and change order versions and their respective order values like so

60017 v.3                250
67001 v.1                  76

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
You can do this with no code at all.  Please refer to the sample workbook attached.

1) A1:B9 contains your original sample data, plus a couple of extra rows I made up

2) In C2, use this formula to pull out the original PO#, and copy down as far needed:
=LEFT(A2,FIND(" v.",A2&" v.")-1)

3) In D2, use this to find the change order number (returns 0 if there is no change order):
=IFERROR(MID(A2,FIND(" v.",A2&" v.")+3,LEN(A2))*1,0)

4) In E2, use this array formula to determine if the current row is the "max" row for that PO:

Note that this is an array formula, so enter it without those curly braces, and use Ctrl+Shift+Enter instead of just Enter to finish it off.  Also, adjust the range references in the array formula as needed

5) Copy those formulas down as far as needed

6) Enable the auto filter, and select only those rows that show TRUE in Column E

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.