Macro for finding duplicate ip address from each tab

[IKHAN]

Hello, Using excel 2010 -Need help with a macro to compare multiple tabs in spreadsheet to find duplicate ip address in all tabs and provide output in a end new tab with existing duplicate ip address found , server name associated with ip address and tab name and row number.

Note:Since ip address data has been pulled from different resources, each tab could have hiddden characters or trailing spaces ,column mismatch in each tab,or multiple duplicate ip's in same tab. Do have at least 20 tabs in my spreadsheet to find duplicate ip address and server names.

Help is really appreciated...

 

[Nebu]

Hi:

Here is a macro to identify duplicates. this macro will not identify row and sheet names . To identify row or sheet name you need to have more loops and it will take more time if your data set is more.Anyhow here is the macro, click on the run button to get the results.

Thanks

 

[IKHAN]

Thanks for replying..

I have to identify the sheet and row# for me to highlight and filter the duplicates out from other sheets, As mentioned - have atleast 20 sheets of data in workbook
And sheet names could be named different too , not always sheet 1,sheet 2,sheet 3...

 

[Marc L]

Hi !

According to the attachment mentaly insane worksheets design
(columns order, some spaces ending ip addresses ‼),
paste this demonstration code to the output worksheet module :​

Sub Demo()
            Dim Ws As Worksheet
            Application.ScreenUpdating = False
With Me.UsedRange.Rows
    If .Count > 1 Then .Item("2:" & .Count).Clear
End With
            COL = [A1:B2].Value
             E% = 1
             L& = 1
With CreateObject("Scripting.Dictionary")
    For Each Ws In Worksheets
        If Ws.Index <> Me.Index Then
            For C% = 1 To UBound(COL, 2)
                COL(2, C) = Application.Match(COL(1, C), Ws.UsedRange.Rows(1), 0)
            Next
            If IsNumeric(Application.Sum(Application.Index(COL, 2))) Then
                    VA = Application.Trim(Ws.UsedRange.Value)
                For R& = 2 To UBound(VA)
                    If VA(R, COL(2, 2)) > "" Then
                         V = Array(VA(R, COL(2, 1)), VA(R, COL(2, 2)), Ws.Name & " row " & R)
                        If .Exists(VA(R, COL(2, 2))) Then
                                       W = .Item(VA(R, COL(2, 2)))
                            If IsArray(W) Then
                                L = L + 1
                                .Item(VA(R, COL(2, 2))) = ""
                                Cells(L, 1).Resize(, 3).Value = W
                            End If
                                L = L + 1
                                Cells(L, 1).Resize(, 3).Value = V
                        Else
                            .Add VA(R, COL(2, 2)), V
                        End If
                    End If
                Next
            Else
                E = E + 1:  Cells(E, 5).Value = Ws.Name
            End If
        End If
    Next
            .RemoveAll
End With
            Cells(1).CurrentRegion.Sort Cells(2), xlAscending, Header:=xlYes
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​