Formula or Code to grab the next highest number

[TexasBobcat]

Excel Gods,

I am in dire need of assistance.

I have this particular spreadsheet with 9 columns: Policy#, Iss St, G Issue, Iss Year, Prem Collected, Claims Paid, Infct, Agent Level, and Agent number.

Thing problem with this spreadsheet is that In column A, Policy #, there are duplicates. When there are duplicates it is because there was more than one agent who helps the policy.

***What I want is to get rid of all the duplicates, and... get the highest agent level after one.
For example: Policy Number 3082695 has three rows with corresponding agent level # 1,3, and 7. I only want the row that has the three because it is the next highest level after 1.

If a policy doesn't have a duplicate than, I want that policy regardless.
4Q2013DiamondPartnerforExcel10302015.xlsx

Maybe a loop? Im not sure

[skywriter]

This is a little slow, it takes about 5 seconds on my computer but it's the best I can do.
You have more than one policy with duplicate agents with the 1 and no other agent number higher.

Please Login or Register  to view this content.