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

dagoof

New Member
Joined
Sep 3, 2006
Messages
29
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! :unsure:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
first of all you need to post what are those ranges the duplicates could be found from your spreadsheet.
 

dagoof

New Member
Joined
Sep 3, 2006
Messages
29
Thanks for the reply :)

Well, as I said, data differs from sheet to sheet but I will manually sort it so that all zip codes run in column G (some sheets are 50 rows, a couple 400, 1 is 800) so the range will be G2 onwards - I'm think zip codes would be the easiest basis for identifying duplicates (though I would prefer not to remove them automatically as 2 customers could be in same zip code).
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Check out the HTML-addin to convert your sheet and post it on the board. (it's a sticky post at the top of this forum)
If we can see the data it's probably a matter of minutes(seconds maybe :eek: ) before we got you a solution.
 

dagoof

New Member
Joined
Sep 3, 2006
Messages
29

ADVERTISEMENT

Hi Harvey,

Thanks, that would be nice if it saved me 4 hours of copy & pasting! :LOL:

Will get that up ASAP.
 

dagoof

New Member
Joined
Sep 3, 2006
Messages
29
Hi Harvey,

Ok, can't put the real data in for obvious reasons but here's the general make up of the 2 files as they stand:
type1.xls
ABCDEFG
1NAMECOMPANYADD1ADD2ADD3ADD4POSTCODE
2joeabccothisstreetthatstreetsomewherethereD254JK
3janeCDEcothatstreetthisstreetanywherehereW233LD
scc 23-08-06 sample_requests
type2.xls
ABCD
1Company+NameAddress1Address2
2AB&Co(JoeSmith)123TheirStreet,TheirTownTheirCity,X344SD
3TIRCompany(JaneBrown)684TheirStreet,ThatTownBigcity,G233LF
Sheet1
[/code]
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215

ADVERTISEMENT

Well, this is not an elegant method, but it works for me. Add a new column to the right of your data called "Duplicate". Then, sort the
range by something that should give you a unique ID for each row, such as Company + Name in your example. Then copy this formula in the Duplicate column:

if (a2=a1,0,9)

and copy it down. Next, copy all the data (including the duplicate column) into a new sheet MAKING SURE YOU PASTE AS "PASTE SPECIAL VALUES". Then sort by the Duplicate column; all your duplicates will still be there (so you can check them or delete them as you wish), with all your unique items neatly sorted for you.

Hope this helps
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
Well, this is not an elegant method, but it works for me. Add a new column to the right of your data called "Duplicate". Then, sort the
range by something that should give you a unique ID for each row, such as Company + Name in your example. Then copy this formula in the Duplicate column:

if (a2=a1,0,9)

and copy it down. Next, copy all the data (including the duplicate column) into a new sheet MAKING SURE YOU PASTE AS "PASTE SPECIAL VALUES". Then sort by the Duplicate column; all your duplicates will still be there (so you can check them or delete them as you wish), with all your unique items neatly sorted for you.

Hope this helps
 

dagoof

New Member
Joined
Sep 3, 2006
Messages
29
Hi Polarbear,

Thanks for the advice - this would just let me find duplicates within a single sheet though, wouldn't it? I am more looking to compare between files/sheets.

And on what basis would this method be comparing fields? As the company name and contact name are joined I think the zip code would be the best way (but I need to separate that too). Maybe if someone could offer a way of seperating the zip code that would make it easier for the next stage of comparing between sheets.
 

riaz

Well-known Member
Joined
Jun 27, 2006
Messages
779
Thank you for raising this question. I inherited a similar mess just this morning and behold, there is a matching question on the forum. Never ceases to amaze me.

I found yet another solution (thank you Texasalynn) in my searches. Have not tested it yet, but like the post say, backup before trying.

http://www.mrexcel.com/board2/viewtopic.php?t=231706

I will go and play with all these solutions. My layout is slightly different, and I have managed to combine all the mess ... er, data ... into one spreadsheet, so at least I don't have to criss-cross between spreadsheets.

Good luck.
 

Forum statistics

Threads
1,141,731
Messages
5,708,151
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top