Duplate count over multiple rows and columns

[brokian42]

Hi,

I have a spreadsheet of IP addresses 528 rows x 56 Columns (not all rows are full), I need to get a count of the number of duplications. An advice on how I can complete this?

Thanks in advance

 

[Joe4]

Easy way:

Copy data over to another sheet.
Apply Excel's built-in "Remove Duplicates" functionality
Compare record count on this sheet to original sheet. The difference is your number of duplicates.

 

[brokian42]

Sorry should have explained better, need to count how many times each of the IP Addresses appears in the original data

 

[Joe4]

Just use COUNTIF, i.e.
If you wanted to count the number of times the value in cell A2 appears in column A, it would look like this:

=COUNTIF(A:A,A2)

 

[MickG]

This will provide a list in sheet2 (starting "A2") of addresses and count.

[COLOR="Navy"]Sub[/COLOR] MG09Feb55
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:BD528").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
  ReDim Ray(1 To .Count, 1 To 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        c = c + 1
          Ray(c, 1) = K: Ray(c, 2) = .Item(K).Count
    [COLOR="Navy"]Next[/COLOR] K
Sheets("Sheet2").Range("A2").Resize(c, 2) = Ray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brokian42]

Thanks Mick,

That seems to have done the trick

 

[MickG]

You're welcome