Deleting Duplicate Rows???

I am hoping someone can help me. I have a spreadsheet that I need to delete duplicate rows in. However, in order to determine if a row is a duplicate I need to check 2 cells per row. In the attached file you will see that each row has 4 cells. I need to compare the cells in columns B and C with the B and C cells of the Row beneath. If the B and C cells match then it is considered a duplicate and one of the rows needs to be deleted. Also, there may be multiple duplicate rows.

For an example see rows 17, 18 and 19. I only need 1 row to remain.

I am looking for a vb script that would analyze a file with thousands of rows and delete the duplicates. Any feedback or suggestions would be greatly appreciated.

Hi - the below code will systematically take each 2 cell pair and compare to all remaining 2 cell pairs, deleting duplicates as it goes (I assumed your list of data was not sorted, but this will work even if it is).

Please Login or Register  to view this content.

If you have blank rows in your data then the CurrentRegion property may not work, even after you adjust your start row per the comment. You could use this to get the last row of data:

Please Login or Register  to view this content.

But - if you have things like blanks in subsequent rows then (without if conditions to ignore matches on blanks) these too would be deleted. depending on the nature of your data, you might find a better "special cell" definition, based on format or something else ... have a look at the online help if this is a problem ..

Hope this helps ... MM.

Hi MM,

What if you have columns and you have to compare 4 or 5 rows in it?

Thanks.

I am sorry. I meant a different thing. What I was trying to ask was - what if you have 8 columns and you have to compare 4-5 columns?

Hi Mia .. I'm not sure if this is a different problem or not ... the code below will delete multiple duplicate rows based on whether or not the cells in 2 columns are the same for the rows being compared ...

If this isn't what you mean, then I suggest you start a new post and explain what you're trying to do

MM.

Ah - I see ... this could be expanded, but you'd have to tell me what your match criteria is .. is it based on 4 columns or 5 or if it's sometimes 4 and sometimes 5 then what are the conditions?

MM.

I have tried to run the macro a couple of times but in both cases it runs continuously and eventually Excel stops responding. The file I am deleting dupes in is 70,000 rows long. I think this may be causing the problem because when I run the macro on a much smaller file (100 rows) it seems to work ok. Is there anything you would suggest other than breaking the data into smaller pieces? Is there any way to rewrite the script such that it would not get so bogged down?

I am running this on a relatively new machine with 4gigs of memory so I don't think it's the hardware.

Hi - sorry about that ... I didn't realise your dataset was so large; if you send me a sample of the data I'll try to optimise it .. and no - it won't be your hardware :-) MM.

Hi jerickson .. I've reworked this slightly so that it will now run more efficiently; i've tested it on 5000 lines and it runs in a few seconds.

Please Login or Register  to view this content.

Let me know how you go. MM

That did the trick! Thanks MM for your help.

One thing I wanted to ask. I had sorted the data in A->Z order prior to running the script for Column A then B then C. After I ran the script the data was in a strange order and I had to sort it again. Is that expected? Should I be concerned about data loss? I'm pretty certain it's working fine but I thought the way it altered the order was strange. If it's working then it's not a big deal as I can just add a sort to the end of the script. I was just curious why it changed the order.

Hi - glad that works for you:-)

I had to run a sort on B then C prior to reading in the data so that it didn't need to select every value and check it against every other value; sorting it means i know when the next value is not the same i can stop searching any remaining rows. Make sense?

In any case, if you add the following at the end, your sort order will be restored:

Please Login or Register  to view this content.

Cheers, MM.

That does make sense...thanks again. BTW, my wife and I used to live in Sutton Coldfield outside of Birmingham. Work transfer.

Excellent - glad that's done the trick:-) I'd like to say "nice to hear from a fellow Englishman .." but I'm from Oz ... work transfer too. Not enjoying what you chaps generously refer to as "summer" here, but the proximity to Europe more than makes up for it. All the best & good luck. MM.

Hello jadegreenwolf, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.