Delete Duplicate Entire Rows Keeping Row with Lowest Price

[timmy1254]

Hi Guys,

I have a database with a ton of duplicate entries where each column is the exact same data in multiple rows except for the price column. I would like to figure out a quick way to delete duplicates keeping the row with the lowest price. I can sort to make it simpler. i have attached a spreadsheet with a sample of data.

Thanks

Also, does anyone know a good advanced excel book to learn macros and VBA?

[tigeravatar]

timmy1254,

Give the below code a try. As for books to learn, afraid I can't help you there

Please Login or Register  to view this content.

[mojo249]

Very nice solution tigeravatar.

[tigeravatar]

Thank you

[timmy1254]

That worked perfect! Thanks alot! Could you tell me what part of the code I would alter if I add or subtract columns from the sheet? Not the price column, of course, but I may need to delete a column or add a column in the future and it'd be nice if I could alter the code myself.

[tigeravatar]

timmy1254,

The code already self-adjusts. All you need to do is make sure the BookCol and PricCol are set to the correct column letters

[timmy1254]

Actually I noticed a problem. You wrote it so that it would delete duplicates based off the title column. I need it to delete duplicates based off the first Item ID column. There are some titles that have different Item ID's.

[timmy1254]

Nevermind I figured it out. Very easy code thanks alot!

[matrix2280]

Found this thread and is exactly what I am needing, but I need to see how to modify this in order to include one additional column.

I have attached my example on this and are not really sure what I need to modify in the code to get this to work. I added a new Const called FeeTab which is my Column A, but not sure how to include it in the FormulaArray.

Breakdown:
There are four columns
A - FeeSched: This is the insurance company name
B - Code: This is a list of CPT/HCPC codes for each insurance company listed in Column A. There can be/probably will be duplicates each time.
C - APC: This column may or may not have values; however, I do not believe this should be factored in.
D - Charge: This is the charge.

Goal:
The goal I am trying to accomplish is the code should check Columns B and D, and remove the "lowest" value if a duplicate "Code" (Column B) is found; however, the catch is it should take into consideration Column A.

In the attached, There is a duplicate code for Aetna (10022) that has a charge of 100 and 105. The expected result would be to remove the code for Aetna that has the charge of 100 and leave all of the others.

Hopefully my case makes sense, and appreciate any help If this should have been a new topic/thread I apologize; however, figured since this was related I would place it here.
If any clarifications are needed, let me know.

Thank you,Excel Example.xlsm

[matrix2280]

How can you adjust the code to keep only the highest value?

[FDibbins]

matrix, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.