Duplication??

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
Hi All,

i would like to check a column for duplicates, its a very large amount of data roughly over 5000 names that i have had to enter and want to make sure i've not done any twice, so i would like a macro button that searches through column C and colours duplicates in RED

Kind Regards,
David
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Talking of duplication, you seem to have entered this thread 4 times at least.

Conditional formatting will do what you want, with a condition formula like:
Code:
=COUNTIF($A$1:$A$5000,A1)>1
 

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
Sorry about duplicate posting! Browser appeared to have stopped so tried pressing button a few times, obviouslly 4 times, sorry everyone!! thats fo hint cheers

Kind Regards,
David
 

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
Hi i have tried this Condition formula and it doesn't work

=COUNTIF($C$1:$C$5000,C1)>1

Could it be that i'm trying to check names rather than numbers?
All i'm trying to do is shade in duplicate names or people with same surname and initials?

Any help would be appreciated

Kind Regards,
David
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

Could it be that i'm trying to check names rather than numbers?

that has nothing to do with it ... it works fine for me.

So, tell me the exact steps you take when applying the Conditional Formatting, and I'll see if I can spot the mistake.
 

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
i select the C Column, format, conditional formating.

Set it as formula is, then the formula you supplied, modded to C column and then choose formating shade red with bold white text.

Get odd results shading cells that aren't duplicates, or not shading them at all!!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

When you select column C, which cell is the active cell? ( this isn't necessarily C1 )?
 

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
no prob isn't C1 i thought due to highlighting complete range would cancel that out?
 

WILFY06

Board Regular
Joined
Oct 13, 2006
Messages
99
I've tried inputting it from C1, and ur right works now thanks for your help Glenn
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top