Finding/moving duplicate entries across multiple sheets

**madbloke** · 12-17-2007, 08:26 AM

Hopefully someone here can help, or at least give me a starting point, as I'm a bit lost as to how to do this.

I have 2 workbooks, each with 9 sheets. I need to merge the 2 workbooks (I can figure that bit out myself - I'll do it manually if need be).

The problem is then that there are likely to be duplicate entries across all 9 sheets (i.e. an entry on sheet 1 could have duplicates on sheets 3 and 5).

What I need to do is find these duplicates, and then move them so that they are under the original entry (and also delete them from where they originally sit)

Hope this makes sense, and that someone can help.

**rylo** · 12-17-2007, 09:07 PM

Hi

Have a look at the attached.

I've assumed that column A is the lookup reference. The macro is called aaa. See if this gets you going.

rylo

**madbloke** · 12-18-2007, 04:57 AM

Originally Posted by rylo

Hi

Have a look at the attached.

I've assumed that column A is the lookup reference. The macro is called aaa. See if this gets you going.

rylo

That is exactly it! You are a star!

Can I be a tad cheeky and ask for a bit of an explanation of what's going on with this one? I can follow it to a point, but I'd like to fully understand this one, to increase my (limited) knowledge a bit.

**rylo** · 12-18-2007, 06:50 PM

Hi

What it is doing is taking each sheet's data, and comparing it to the data on all the following sheets. So the outer loop is starting at sheet1, and cycling through all the sheets except for the last one, as the last one won't have any further sheets to cover

Please Login or Register  to view this content.

It then selects the first sheet.

Now it has to look at all the entries in column A

Please Login or Register  to view this content.

This has to be compared to all the following sheets, so the second loop covers the sheet selection aspect.

Please Login or Register  to view this content.

It then tries to find the value from the first sheet in the comparison sheet. I've used the find function for this.

Please Login or Register  to view this content.

It then tests the result of the find. If it finds a match, it copies the entire row, insert pastes the row after the existing entry, then deletes it from the check sheet.

Please Login or Register  to view this content.

One thing is that as it puts the subsequent item under the item being tested, the subsequent items will not be in the same order as they were originally found. So in the example file, a is found on all 3 sheets. At the end, the sheet order of the a items will be 1,3,2. To see what I mean, put a 1 in column B against all the items on sheet1, 2 for sheet2 and 3 for sheet3. Check the order of the output.

Hope that makes sense.

rylo

**GlenH3294** · 01-23-2013, 02:04 PM

Hi, I'm having the same issue except I just want the duplicates to be highlited. What needs to be changed in the sample macro. I'm afraid I have never used macros before so simplicity is needed.

Thank You

Glenn

**jeffreybrown** · 01-23-2013, 02:13 PM

Hi Glenn & Welcome to the Forum,

Seeing this thread is over 5 years old, your chances of getting more exposure into your query will significantly increase if you create your own thread.

Also, please take a few minutes to look over the Forum Rules as this is one of our rules.