Locating Duplicate Cell Data with a Cell Counter and Highlight

Hi Excel Forum Team,

I wanted to find out, I have about 5000 server names that I’m totaling up. Some of those server names are duplicates, but their hard to locate and to count up how many duplicates there are. So I have two questions to ask.

Question 1)
Is there an equation or way to make a counting cell that will locate duplicate information in a range of cells and thus provide a total number for all the duplicates it was able to locate?

Example:
Cell A1 is the duplicate counting equation cell.
Cells A2 thru. A6 are your data range cells containing your 5 server names.
Cells A3 & A5 have duplicate server names, A2 & A6 have duplicate server names and cell A4 does not have a duplicate server name.
So, cell A1 (equation cell) would only count the duplicate server names and equal a total of 2.

Question 2)
Given a the same range of cells, is there a way for excel to automatically highlight the cell(s) in one color (green) for non-duplicate cells and recognize duplicate cells in another color (yellow)

Thank for taking the time in reading my request and help.

Garrett

if i have understood your requirements correctly, see if this is something that will help you...

it is a very rudimentary approach - some expert here may be able to provide a tighter solution.

three things about this approach:
- a helper column is required.
- your 'servers' list needs to be alphabetically sorted.
- you can start with your entire list being highlighted with a green background. conditional formatting rule will then kick in to change appropriate cells to yellow.