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!

 

[agihcam]

first of all you need to post what are those ranges the duplicates could be found from your spreadsheet.

 

[dagoof]

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]

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 ) before we got you a solution.

 

[dagoof]

Hi Harvey,

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

Will get that up ASAP.

 

[dagoof]

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
	A	B	C	D	E	F	G
1	NAME	COMPANY	ADD1	ADD2	ADD3	ADD4	POSTCODE
2	joe	abcco	thisstreet	thatstreet	somewhere	there	D254JK
3	jane	CDEco	thatstreet	thisstreet	anywhere	here	W233LD
scc 23-08-06 sample_requests

type2.xls
	A	B	C	D
1	Company+Name	Address1	Address2
2	AB&Co(JoeSmith)	123TheirStreet,TheirTown	TheirCity,X344SD
3	TIRCompany(JaneBrown)	684TheirStreet,ThatTown	Bigcity,G233LF
Sheet1

[/code]

 

[PolarBear]

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]

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]

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]

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.