Identifying duplicate cell values in two columns, and listing separate corresponding column data together

Question

I'm working with VBA for excel and I am trying to compare two columns of data, identify what row the matching data is in, and then listing the data from a corresponding column from the same row with corresponding data from a column that matches the row of the value being tested.

For example

___ A_________B_________C__________D___________E

New York______1_________ 2 _______ Dallas _____ _ New York,

Chicago ______3 _________ 3 _______ Seattle _____ Chicago, Seattle, Houston,

LA __________ 4 _________ 3 _______ Houston _____ LA, Denver,

Boston _______ 5 _________ 4 ______ Denver _______ Boston,

Column A & B's value are linked, and columns C & D's values are linked. I'm trying to find a way to find matching values in columns B & C, and then create a single column of the corresponding A & D values listed together, delimited by semicolons.

Answer 1

create a single column of the corresponding A & D values listed together, delimited by semicolons.

Do you mean that "single column" is column E for the result as seen in your post ? If yes, maybe something like this ?

Sub test123()
Set sh1 = Sheets("Sheet1") 'change as needed
Set Rng = sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))

For Each cell In Rng
conc = cell.Offset(0, -1).Value
Set c = Range("C:C").Find(cell.Value, lookat:=xlWhole)
    If Not c Is Nothing Then
    FirstAddress = c.Address
        Do
        conc = conc & ";" & c.Offset(0, 1)
        cell.Offset(0, 3).Value = conc
        Set c = Range("C:C").FindNext(c)
        Loop While c.Address <> FirstAddress
    Else
    cell.Offset(0, 3).Value = conc
    End If
Next
End Sub

The code assumes that each value in column B is unique (as seen in your sample data in column B).