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!

 

[dagoof]

Hi Riaz,

Thanks very much for pointing that out, hope it does the job

Just wondering how I adapt that to be suitable for my purpose though? What do I input to tell it which files it should be comparing? I take it this will still be dependent on me seperating my zip code field?

 

[riaz]

Hi Dagoof

Looking at Texasalynn's code, it starts with sheet 1 in your workbook, works down column B in that sheet, and looks for a matching duplicate in column B in sheet 2. If it finds one, it deletes that row in sheet 2.

And so on, ad infinitum, until it finishes all the sheets in your workbook.

So, my suggestions:

FIRST, MAKE A BACKUP
2) Take the sheet with the "good" data and move it to position 1 in the workbook
3) Decide which column to compare. I agree that zipcode is probably not the way to go as you may have duplicate zips but different addresses.
4) Just a suggestion, but you could try changing

Cells(2, 2).EntireRow.Delete

to say

Cells(2, 2).EntireRow.patterncolourindex=36

which should highlight (in gray) possible duplicates for you to look at rather than deleting them off its own accord.

I see from the two zip codes you supplied that these could be British or Canadian. If so, they usually follow a sequence of 3 chars, space, 3 chars. It should be a simple matter to extract the last 7 chars from your city/zipcode potpourri. Something like
=if(len(a1)>7,right(a1),7)
should check if there is other junk mixed in, and if so, extract the zip. For cities mixed in, you could try
=if(len(a1)>7,left(len(a1)-7)

(Doing this in a hurry, untested. Substitute your own cell ref for a1, and copy down).

HTH

 

[dagoof]

Hi riaz,

Thanks for taking the time to explain that - I realise it must be frustrating when dealing with a newbie like me

Yes, I'm in the UK - 70% of zip (post) codes are 6 digit as in my example but the remain 30% are 7 digits e.g. TR14 3HD

However, I do think post code is going to be my best point for comparison considering that the name and company fields are stuck together, as are 2 lines of the address - this could be difficult to pick up for duplicate detection, a comma or digit or abbreviation may throw it off course.

My data is only a couple of thousand entries in total and geographically diverse so I think postcode is best - it should only throw up 100 or so duplicates which I don't mind manually checking.

So, I've just manually seperated the post codes in the 2 'odd' sheets

 

[riaz]

Hi Dagoof

I'm as much of a newbie as you are, but I have learned a lot in this forum and am just passing it on. Its not frustrating at all, so don't worry about it.

I'd forgotten about the 7 digit zips (long time since I lived in the UK), but now that you have separated your zips, its should be easier. If you go the "highlight" route rather than the "delete" route, that will leave final control with you on whether to wield the axe.

As for my own mess, I have found the ideal solution - I delegated it to someone else

Kind regards

 

[dagoof]

Hi Riaz,

No, trust me, I'm way more teh noob
I'm trying to figure this stuff out but I know virtually nothing about Excel and unfortunately time is not on my side, I have to get this done by tomorrow!

Looking at Texasalynn's code:

Sub SDupDel() 
    FirstWS = 1 
    LastWS = Worksheets.Count 
Worksheets(LastWS).Activate 
LI = Range("B65536").End(xlUp).Row 
For df = 1 To LI 
    di = Cells(df, 2).Value 
    For i = FirstWS To LastWS - 1 
        Worksheets(i).Activate 
        LR = Range("D65536").End(xlUp).Row 
        If Cells(i, 2).Value = di Then 
        Cells(2, 2).EntireRow.Delete 
        End If 
    Next i 
Next df 

End Sub

Firstly, for the line "LI = Range("B65536").End(xlUp).Row", as all my post codes are in the G column would I change the ("B65536") to ("G65536")?
(likewise for LR = Range("D65536").End(xlUp).Row)

Also, I will change it to highlight rather than delete but is Cells(2, 2) like a co-ordinate for Column B, row 2? In other words, to make this suit my data using column G, do I want to change that to Cells(7, 2)?

Lastly, how do I enter this code? Tried pasting it into a cell which didn't work. Also tried adding as a function but couldn't figure that out either

 

[dagoof]

 

[dagoof]

^^^

 

[riaz]

Hi Dagoof

Sorry I wasn't around to help as I was travelling and only got back late last night. I hope your deadline was a bit flexible.

1) easist way to get this into your workings is to go

tools, macro, record new macro. You will see a little button bar with a stop sign, much like on cassette and cd players. Just do something (like setting the font in A1 to bold) then press the stop button. You will be asked to name the macro - give it a name you like.

Next, go tools, macro, edit, select the above macro. Take out the lines you see there and paste the code we've been talking about.

To use the macro, you go tools, macro, run, select the macro and you're there.

2) Specifically to your questions:

a) Yes, change the B65536 to G65536.
b) Likewise for the D65536
c) The coordinates are row, column so you would change this to 2,7.

Make sure you are not deleting anything, just using the color index so that if anything does not work to your liking, you can always roll back to your previous situation.

HTH

 

[dagoof]

Hi Riaz,

No, that's ok, wasn't expecting you specifically to be around - I know plenty of people don't want to look at excel on weekends

Deadline was kind of flexible - I got a duplicate counter and went through it all manually but still have the 3rd sheet to do so still really want to get this working!

So thanks for all the advice. I'd put the 3 files into 3 worksheets and shifted all postcode columns so they were in G. I then used your advice to run the following Macro:

Sub SDupDel() 
    FirstWS = 1 
    LastWS = Worksheets.Count 
Worksheets(LastWS).Activate 
LI = Range("G65536").End(xlUp).Row 
For df = 1 To LI 
    di = Cells(df, 2).Value 
    For i = FirstWS To LastWS - 1 
        Worksheets(i).Activate 
        LR = Range("G65536").End(xlUp).Row 
        If Cells(i, 2).Value = di Then 
        Cells(2, 7).EntireRow.patterncolourindex=36
        End If 
    Next i 
Next df 

End Sub

It runs and skips onto the 2nd worksheet before showing:
"Runtime 438 - object doesn't support this property or method"

Is there something there I've missed?
Again, thanks for the help

 

[dagoof]