Removing duplicates

[Dr Dee]

Hey guys,
haven't been here for a while, guess I'm improving at this Excel stuff, eh?
I do have an issue I've been battling with - I've got a column of names (Surname, Initial) that is an amalgamation from three different sheets. I go to "Remove Duplicates", but keep getting the message "No duplicate values found".
There clearly are duplicates (to the eye) but obviously there is some sort of formatting issue (I think) that's not getting me where I want.
I know you guys are gonna make me look like a goose...again but I'm sure I will learn some more of this intriguing app.

Thanks,
Den

 

[scottylad2]

Try the countif first, and here you see Fred is showing twice but only shows as 1 with the countif

Excel Workbook
	D	E
14	Fred	1
15	Ronnie	3
16	Archie	1
17	Mitch	2
18	Ronnie	3
19	Mitch	2
20	Fred	1
21	Ronnie	3
22	sam	1
23	edward	1
Sheet2

Excel 2010

Cell Formulas
Range	Formula
E14	=COUNTIF($D$14:$D$23,D14)
E15	=COUNTIF($D$14:$D$23,D15)
E16	=COUNTIF($D$14:$D$23,D16)
E17	=COUNTIF($D$14:$D$23,D17)
E18	=COUNTIF($D$14:$D$23,D18)
E19	=COUNTIF($D$14:$D$23,D19)
E20	=COUNTIF($D$14:$D$23,D20)
E21	=COUNTIF($D$14:$D$23,D21)
E22	=COUNTIF($D$14:$D$23,D22)
E23	=COUNTIF($D$14:$D$23,D23)

which is because one of the Freds has extra spaces. use the Trim function to make sure you have no leading or trailing spaces

Excel Workbook
	F
13	After Trim
14	Fred
15	Ronnie
16	Archie
17	Mitch
18	Ronnie
19	Mitch
20	Fred
21	Ronnie
22	sam
23	edward
Sheet2

Excel 2010

Cell Formulas
Range	Formula
F14	=TRIM(D14)

then try the Advanced Filter and copy unique records only

Excel Workbook
	G
13	Adv Filter Result
14	After Trim
15	Fred
16	Ronnie
17	Archie
18	Mitch
19	sam
20	edward
Sheet2

Excel 2010

 

[Dr Dee]

Excellent stuff, Scotty, I guess there must have been trailling spaces..obviously I couldn't see these, but at least I have some more insight into 'why' sometimes the obvious isn't so obvious; often so common with Excel.
Thanks mate....

 

[scottylad2]

It happens a lot to data thats imported into excel from another programme.

Glad to help

 

[Rasm]

Try and Trim(strings) - will remove spaces in beginning and end - also use
Option Compare Text in declaration of your code - this will make string comparisons case insensetive - just some ideas - good luck