Compare two lists and return multiple values

[jgoldkamp]

Hello,

I am trying to compare Column A in the sheet "Craigslist" to Column A in the sheet "VPDC Server Data" and then return all the names from Column G where the two match in a new sheet titled "Craigs VM List" in Column B.

I would also like to return the corresponding "Craigslist" Column A value to Craigs VM List" in Column A.

I've done something like this before, but I can't remember how I did it...

Here is a link to the document as a Google Sheet:

https://docs.google.com/spreadsheet...dZ3yOFTMSV7ntRuyEiq2ciR54aU/edit#gid=32454337

Below are the screenshots of the data, if the google doc won't work for you:

- Album on Imgur

I also posted this question on OzGrid, but did not receive any solution: Compare two lists and return multiple values

 

[JLGWhiz]

Assumes no duplicates in either sheet 1 or 2.

Sub compilenew()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("Craigslist")
Set sh2 = Sheets("VPDC Server Data")
Set sh3 = Sheets("Craigs VN List")
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = fn.Offset(0, 6)
            End If
    Next
End Sub

 

[jgoldkamp]

Assumes no duplicates in either sheet 1 or 2.

Sub compilenew()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("Craigslist")
Set sh2 = Sheets("VPDC Server Data")
Set sh3 = Sheets("Craigs VM List")
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = fn.Offset(0, 6)
            End If
    Next
End Sub

Hello,

Thank you for your reply, however there are duplicate Customer Numbers in the VPDC Server Data list. That's why I was finding this so difficult. I need an output with all of the hostnames that correspond to the matching customer numbers.

 

[JLGWhiz]

This should get the additional entries in sheet 2.

Sub compilenew2()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range, fadr
Set sh1 = Sheets("Craigslist")
Set sh2 = Sheets("VPDC Server Data")
Set sh3 = Sheets("Craigs VN List")
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fadr = fn.Address
                Do
                    sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                    sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = fn.Offset(0, 6)
                    Set fn = sh2.Range("A:A").FindNext(fn)
                Loop While fn.Address <> fadr
            End If
    Next
End Sub