Finding Duplicate Names in same column

[philb99]

Hi - I would like some help in finding duplicate names in the same column then highlighting them and if possible counting them.

Names are formatted like

Surname then Initials - Smith P or Smith AR

Can anyone help please

 

[mrhstn]

You could try like this

	A	B
Names	Count
Smith P
Jones A
Smith AR
Chan B
Jones A
Sabir G
Sabir G
Chan B
Mark T
Brown B
Jones A
Angus I
Smith AR
Donell Q

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=COUNTIF($A$2:A2, A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

To highlight the cells use a formula to determine the format in the conditional formatting dialogue and use..


=COUNTIF($A$2:A2, A2)>1

 

[philb99]

Works just great - thanks

 

[mrhstn]

You're very welcome.

You can also highlight duplicates by choosing this option in the CF dialogue. It would highlight every duplicate, the above will not highlight the first instance of the name, just all subsequent duplicates.