Need help with duplicate mess (another sorry) Almost there!

[dagoof]

Almost there - see last post on page 2!

Was wondering if anyone could help me with the mess my data is in - I've tried going through the search but as I'm new to Macros, I'm not really understanding what I'm reading. I've found a couple of close things to what I require but nothing spot on.

In a nutshell, I have maybe 5 different spreadsheets with customer info for my small business. I need to remove duplicates (or preferably have them all highlighted...or even a list made of any duplicates so I can manually check them).

So, going between workbooks is the first problem - the other is in discerning what is a dup. For example, I have a couple of 'good' spreadsheets where everything piece of info is in a seperate field but on a couple of sheets the e.g. city and zip code will be in the same field. I know, pain in the ***, eh? I guess I could go through these and manually separate the zip codes manually if it makes things easier.

Would really appreciate if anyone can offer any advice on this - even point me in the direction of a relevant post or tell me what specifically I should be searching for. I've been trying to figure out how to do this for a few days now and all I've got out of it is a headache!

 

[riaz]

Hiya

I have tested the attached on test data with postcodes in column G, two worksheets, the "master" being sheet1. It runs through each postcode on sheet 1 and compares it to every postcode in the second sheet. I have tried to code in so that it will work regardless of how many sheets in the workbook, so long as the master is sheet 1.

For your interest, you will see where this differs from the previous codes is theorder in which we switch between sheets. If you are running in vba mode, you could open a watch locals window and see the data changing as the macro runs. That would only be for a learning experience, as it will slow the macro down a lot.

Hope this is the solution to your problem. I have been tied up with changing machines (new laptop, same old problems) so did not have time earlier.

I think if this needs any tweaking, you should be able to work this out. If you still need help, shout.

BTW, colorindex 36 is a deep orangey yellow. You may want to play with the colour if you don't want to be blinded. And one pof the errors was due to my having used the British spelling of color.

Kind regards and good luck.

Sub SDupDel()
FirstWS = 1
LastWS = Worksheets.Count
Worksheets(FirstWS).Activate
RI = Range("g65536").End(xlUp).Row
For NI = 2 To LastWS
    Worksheets(NI).Activate
    LI = Range("g65536").End(xlUp).Row
    For df = 1 To RI
        Worksheets(FirstWS).Activate
        di = Cells(df, 7).Value
        Worksheets(NI).Activate
        For i = 1 To LI
        incell = Cells(i, 7).Value
            If incell = di Then
                Cells(i, 7).EntireRow.Interior.ColorIndex = 36
            End If
        Next i
    Next df
Next NI
End Sub

 

[dagoof]

Hi Riaz,

Thank you very much, that is fanstastic and worked a treat! ray:

The only query I'd have for future use is if it could be adapted to highlight all duplicates that occur in any of the worksheets? As it is now, it will compare WS2 against WS1, then WS3 against W1 but not WS3 against WS2. Then it would be perfect!

For just now I just got round this by deleting WS1 and renaming the 2 remaining sheets to WS1/WS2 respectively.

 

[riaz]

Bear with me, I am testing the code before I upload it.

 

[riaz]

Hi Dagoof

I'm glad it worked. I have made the following change in logic. You start with FirstWS as 1, NI looks at FirstWS+1 onwards. When the first worksheet is done with, we change FirstWS to the next number up. The code then loops around. When it increments the number of FirstWS, it checks if NI (the loop counter) is less than the last worksheet number, otherwise it stops. This is to stop it checking the last worksheet by itself (and possibly tearing its hair out).

Two new lines - the screen updating false will not refresh the screen after each comparison, which means the code will run faster. The updating true at the end puts the refresh back on. Use this when you are finalising the code before setting it in stone. In the meantime, while you are testing, leave these commented out (the apostrophe means it will be ignored, so if everything works fine, just take out the apostrophe).

So let me know if you need anything fixed (although by now I think you should have become an expert on this particular piece of code).

Another little tweak - I have added a different colour to each spreadsheet, so you know which duplicate comes from which sheet. If they are all the same colour, you can't tell where the original record was.

Finally, the line beginning "incell=...." and the following line can be combined into one. I used it to check the values coming up when I was testing. I leave it to you to combine them. It will be good practice for you.

Kind regards

Sub SDupDel()
'Application.ScreenUpdating = False
FirstWS = 1
LastWS = Worksheets.Count
Do While FirstWS < LastWS
    Worksheets(FirstWS).Activate
    RI = Range("g65536").End(xlUp).Row
    For NI = FirstWS + 1 To LastWS
        Worksheets(NI).Activate
        LI = Range("g65536").End(xlUp).Row
        For df = 1 To RI
            Worksheets(FirstWS).Activate
            di = Cells(df, 7).Value
            Worksheets(NI).Activate
            For i = 1 To LI
            incell = Cells(i, 7).Value
                If incell = di Then
                    Cells(i, 7).EntireRow.Interior.ColorIndex = 36 + FirstWS
                End If
            Next i
        Next df
    Next NI
    FirstWS = FirstWS + 1
    Loop
'Application.ScreenUpdating = True
End Sub

 

[dagoof]

Hi Riaz,

Thanks very much again for that - I'm afraid I am no expert on any of this as I don't know what the various functions do, but hope to learn enough to get me by. I did if-then etc high school but it was about 10 years ago and whilst I remember some of the concepts I don't remember any of the actual semantics! I can see roughly how it works though, so this has been a great platform to get me started.

 

[riaz]

Funny how things work out. Two days after I did this for you, I needed to do exactly the same thing on a contacts database at work, finding duplicates among all the contacts that have been submitted by 17 staff members and compiled into one workbook.

incell = Cells(i, 7).Value
                If incell = di Then

can be combined into

if cells(i,7).value=di then

Glad it worked out for you.

Kind regards