Compare two spreadsheets

[padinka]

I have two spreadsheets. Servers and Application.

I need to look at a column called IP in the server speadsheet and see if the IP has a Match in the Application spreadsheet's IP column. If so, move to the next row.

If not, then I need to look at a different column of the same row called HostName and see if there is a match on HostName in the Application speadsheet's HostName Column.
If so, move on to the next row.

If it matches neither IP or HostName, then I need to output the whole line from Server to a new spreadsheet.

Someone feel like attempting this for me?


Trisha
padinka@yahoo.com

 

[SkipVought]

Hi,

Something like this ought to work...

Sub LookItUp()
    Dim wsServ As Worksheet, wsAppl As Worksheet, wsNew As Worksheet
    Set wsServ = Worksheets("Servers")
    Set wsAppl = Worksheets("Applications")
    Set wsNew = Worksheets.Add
    With wsServ
        For Each r In .[IP]
            ptr = Application.Match(r.Value, wsAppl.[IP], 0)
            If IsError(ptr) Then
                ptr = Application.Match(r.Value, wsAppl.[HostName], 0)
                If IsError(ptr) Then
                    r.EntireRow.Copy _
                    distination:=wsNew.Cells(wsNew.Cells(1, 1).CurrentRegion.Rows.Count + 1, 1).Value
                End If
            End If
        Next
    End With
    Set wsServ = Nothing
    Set wsAppl = Nothing
    Set wsNew = Nothing
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oops

Destination

Sub LookItUp()
Dim wsServ As Worksheet, wsAppl As Worksheet, wsNew As Worksheet
Set wsServ = Worksheets("Servers&quot
Set wsAppl = Worksheets("Applications&quot
Set wsNew = Worksheets.Add
With wsServ
For Each r In .[IP]
ptr = Application.Match(r.Value, wsAppl.[IP], 0)
If IsError(ptr) Then
ptr = Application.Match(r.Value, wsAppl.[HostName], 0)
If IsError(ptr) Then
r.EntireRow.Copy _
Destination:=wsNew.Cells(wsNew.Cells(1, 1).CurrentRegion.Rows.Count + 1, 1).Value
End If
End If
Next
End With
Set wsServ = Nothing
Set wsAppl = Nothing
Set wsNew = Nothing
End Sub


sorry

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[padinka]

I'm getting a type mismatch error at:

For Each r In .[IP]



Trisha
padinka@yahoo.com

 

[SkipVought]

Hi,

2 things...
1. name you ranges using menu item Insert/Name/Create/Create names in top row.
2. remove .Value from the Destination

Sub LookItUp()
    Dim wsServ As Worksheet, wsAppl As Worksheet, wsNew As Worksheet
    Set wsServ = Worksheets("Servers")
    Set wsAppl = Worksheets("Applications")
    Set wsNew = Worksheets.Add
    With wsServ
        For Each r In .[IP]
            ptr = Application.Match(r.Value, wsAppl.[IP], 0)
            If IsError(ptr) Then
                ptr = Application.Match(r.Value, wsAppl.[HostName], 0)
                If IsError(ptr) Then
                    r.EntireRow.Copy _
                    Destination:=wsNew.Cells(wsNew.Cells(1, 1).CurrentRegion.Rows.Count + 1, 1)
                End If
            End If
        Next
    End With
    Set wsServ = Nothing
    Set wsAppl = Nothing
    Set wsNew = Nothing
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com