Macro to find rows with duplicates, compare cells between them and delete rows.

What happens if the rows to be deleted in "B" have text of the same length ???

Thanks for the quick reply MickG.

Sorry, I forgot to say: If the values in column B are the same then delete all duplicates.

What I realy meant was, if there were 3 duplicates in "B", the length of data in one cell was 3, and the other two where 6, which cell wants deleting.

Thanks again MickG.

Let's say the following rows have identical values in column F and O and have the following in column B:

1 ABCDEF
2 ABCDEF
3 ABC

Then row 2 (ABCDEF) and row 3 (ABC) should be deleted and row 1 (the first in the list of duplicates and the one with the longest, or one of the longest, values in B) should be saved.

Does that make sense, I am not sure if I am explaining myself so well or if what I am trying to do is even possible.

I really appreciate your help though.

Thanks.

MickG,

Thanks, your help has been great.

I have been looking at your code and trying to understand it, but unfortunately I do not understand it all. I am learning how to program macros, but some of this has got me confused.

The first version you uploaded did delete rows in case of duplicates, but it seems to have been looking for duplicates in column B and then deleting everything else (see example attached).

The edited code is causing "Error 91 Object Variable or With block variable not set", I am not sure why, when I click debug it highlights the penultimate line: nRng.EntireRow.Delete

With the old version it was almost working correctly, but in some cases it seems to delete rows even when it should now.

In rows 12 & 13 (see attached file), the old macro deleted both rows, but should in fact keep the one with the longest value in column B.

So:
12333 | RG | 622e76m00007
12334455 | RG | 622e76m00007

should turn into:
12334455 | RG | 622e76m00007

I have attached a new file of examples and have included the macro that is causing the 91 error. The macro has been called "TidyUpExtract".

"upload(30Jul11_fortesting).xls" is the file that contains the original data and "upload(30Jul11_examples).xls" contains the data with both the old macro output and the intended output.

Thanks again for any help that you can offer.

MickG: Thank you very much!

So far I think it is a 100% fit for what I am trying to accomplish. I was testing it yesterday and meant to reply but ran out of time, sorry about that.

I am impressed with you quick reply and really appreciate you help. I am still learning to program macros, and have to admit that I could never have written this on my own.

Thanks for posting this solution, I will try to learn as much as I can from studying this code.

Thank you for your kind responses, they are much appreciated
Regards Mick

Hello again,

Sorry, but I have a follow-up question. After running the code above I need to go back, inspect the file and then clean for values in F that have an identical value in B.

So:

B F
1234 ABCD
12345 ABCDE
123 ABCD
123 ABCD
1234 ABCD

Should become:
B F
1234 ABCD
12345 ABCDE
123 ABCD

I took the code you wrote above and edited only a minor part:

Please Login or Register  to view this content.

Is that a correct and sensible way of doing that? It seems to work, but I am not sure it is the best way.

Also, I realized that I need to run another check after cleaning the data. I need to compare values in F to see if a cell that contains another cell's data also has the same data of column O, and if they do I need to delete the row with the shortest value in F.

Example:

F O
Bart Bevers 12345
Bevers 12345

Would become:

F O
Bart Bevers 12345

I was looking at it and it seems like I could recycle the code from the original macro, but I am not sure how to best go about checking for if values in a cell in F are found in another cell in F.

Thanks, any help is appreciated.

Try this for the first :-

Please Login or Register  to view this content.

and this for the second:-

Please Login or Register  to view this content.

Regards Mick

Thanks again MickG. Sorry for the slow reply, I was gone the last few days.

This is great, the code works exactly the way I wanted it to. I still have some way to go myself.

Again, you have been an incredible source of help. It is much appreciated.

Mod: this thread can be marked solved. Thanks.