Locating Duplicate Cell Data with a Cell Counter and Highlight

[garrett.grillo]

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

[FDibbins]

I know your data range is much longer than this, so adjust as required.

assume your data range is L13:L19, in M13, add this formula, and copy down. please take note of the absoluting ($)...

=IF(ISERROR(VLOOKUP(L13,L14:$L$19,1,FALSE)),"",L13)

Then apply filters and use that column to select all non-blanks. that will give you a list of all duplicates

edit:

to have a running total of all duplicates, change the formula to...
=IF(ISERROR(VLOOKUP(L14,L15:$L$19,1,FALSE)),"",MAX($M$13:M13)+1)

[AlvaroSiza]

If I were approaching this one, I would apply a unique values filter via Data Tab >> Sort & Filter Group >> Advanced. I would then select my range >> Copy to a New Location >> Specify the location to be pasted, enable Unique Values Only and create my list. My 'Count' of the duplicates would then be one less than the offset COUNTIF.

See Attached. ServerNameExample.xlsm

[Sa DQ]

Please Login or Register  to view this content.

[garrett.grillo]

Hi Sa Dq,

Can you explain how the macros work in the spreadsheet you attached?

Thank You,
Garrett

[Sa DQ]

See #4 post:

1:= Rng.Address: Range("A1:B6395")
2:= Rws = 6395
3:= Rng.Address: Range("B1:B6395")
4:= Set BackColor of Rng = 0
5:= Methode AdvancedFilter Rng => [AA] column
6..18 For. . Next with Range in [AA2:AA6291]
7:= Methode FIND() With cell in [AA2:AA6291]
8:= If yes, then. . . (to 17 End If)
9:= MyAdd = sRng.Address (ln 16)
11:= Do. . Loop (ln 16)
12:= jJ Count times duplicate
13:= Count duplicate ID
14:= Set backColor in cell for duplicate
15:= FindNext (If . . .)
16:=Loop . . . .
19:= Add duplication value to [D2]