How to find duplicate entries, find which has the lowest value, and delete the rest?

Hi guys,

I have a spreadsheet which lists all the price changes for the month.
Before doing any updates, I will need to search through the spreadsheet for any duplicates.

If any duplicate rows are found, I need the VBA code to look at the 6th column of those duplicate rows to find out which duplicate row has the lowest value, and delete the rest of the duplicate rows.

For e.g. If there are 4000 rows, and duplicates are found in row 10, 100 and 1000, the code will find that row 100 has the lowest value in the accompanying cell in the 6th column, and delete row 10 and row 1000.

So in the end there will only be 3999 rows left, with no blank rows in between.

I think I would need to use arrays right? I'm still not good with arrays and looking up on it now.

Will appreciate any advice given!

Hi JBeaucaire,

Yes, the column will be sorted alphabetically, and I can sort the columns in column 6 in ascending order too.

I have attached the file here.

I am trying out the following code, but have trouble getting to work properly yet. Not sure if its an optimal solution, but i think we can start from here if you wish.

Please Login or Register  to view this content.

For the intFinalBillingRow, I have issues getting the code to recalculate the final row of the sheet after the duplicates are removed, as it will be used later. Any advice on this?

Thanks in advance!

Thanks for the quick reply JBeaucaire, and WOW it worked beautifully!

That's is one complex code...(to me)
I don't understand this part of the code

Please Login or Register  to view this content.

How does it work?

Also, do you have any suggestions to check if there are non-duplicate rows which are deleted? I'm not doubting your work, but I want to know the sequential thinking behind this.

My logic sucks! LOL

Thanks for the explanation JBeaucaire,

I'm still running though the spreadsheets to make sure everything is fine.
My master workbook has 16,000 rows, and I have 11 spreadsheets to check against with each spreadsheet running to 1000 rows each.

Making sure the updates/deletions are accurate is a headache...if I'm checking it correctly in the first place.

Once done, I'll change the status of this thread.

Thanks again!

Thank you for this code. Very helpful!

I'd like to take the same example and add one additional column into the determination of which rows to keep. In this example I need to keep the lowest price for each combination of Column A and Column D. I've provided updated sample workbook.

Thanks in advance.

Worked like a charm! Thanks a ton.

I'm not seeing a Prefix dropdown. Is that because I did not create the original post?

I have the same problem. Could someone take a look at my attachment and let me know what i need to alter in the code above to make this work for my layout?