If cell contains a specific value from a Column, show a third cell value

Question

Here's what I'm trying to do:

I have 2000 order numbers in A:A, examples:

125787
358946
358961

I have 2000 strings that include these order numbers in B:B, examples:

12542-MARLBORO-125787
19009-BRYN ATHYN-358946
21037-EDGEWATER-358961

I have 3000 person names in C:C that are associated whit the cities listed in column B, examples:

Frank Smith - MARLBORO
John Park - BRYN ATHYN
Kevin Decker - EDGEWATER

I want to match/find the order numbers from A:A in B:B and return the person's name (in C:C) that is associated to that city and put the name in a new column D:D. I hope this make sense...

Answer 1

Or just a formula. In D1, enter

=IF(A1<>"",INDEX(C:C,MATCH(CONCATENATE("*",A1),B:B,0)),"")

Answer 2

Here is a button click event that will do what you are looking for. You will need to add a reference for the adodb recordset. In the VBA IDE, go to the tools pull down menu - References. Select the "Microsoft ActiveX Data Objects 2.8 Library".

Private Sub CommandButton5_Click()
Dim rs As New ADODB.Recordset
Dim ws As Excel.Worksheet
Dim lRow As Long
Dim strCity As String
Dim strName As String
Dim iIndex As Integer

    Set ws = Application.ActiveSheet

    'Add fields to your recordset for storing data.  You can store sums here.
    With rs
        .Fields.Append "Row", adChar, 20
        .Fields.Append "ColumnB", adChar, 70
        .Fields.Append "ColumnC", adChar, 70
        .Open
    End With

    lRow = 1

    'Loop through and record what is in the columns we want to look at
    Do While lRow <= ws.UsedRange.Rows.Count

        rs.AddNew
        rs.Fields("Row").Value = lRow
        rs.Fields("ColumnB").Value = ws.Range("B" & lRow).Value
        rs.Fields("ColumnC").Value = ws.Range("C" & lRow).Value
        rs.Update

        lRow = lRow + 1
        ws.Range("A" & lRow).Activate
    Loop

    If rs.EOF = False Then
        rs.MoveFirst
    End If

    'Now go through and check the values of the second column against what we recorded from the first
    lRow = 1
    Do While lRow <= ws.UsedRange.Rows.Count

        'Find the record with this order number.
        rs.Filter = ""
        rs.Filter = "ColumnB Like '%" & ws.Range("A" & lRow).Value & "%'"
        If rs.RecordCount > 0 Then
            strCity = rs.Fields("ColumnC").Value
            iIndex = 0
            iIndex = InStr(strCity, "-")
            If iIndex <> 0 Then
                strCity = Right(strCity, Len(strCity) - iIndex)
            End If
            iIndex = 0
            iIndex = InStr(strCity, "-")
            If iIndex <> 0 Then
                strCity = Left(strCity, iIndex)
            End If

            'Now find the record with that name
            rs.Filter = ""
            rs.Filter = "ColumnC Like '%" & Trim(strCity) & "%'"
            If rs.RecordCount > 0 Then
                strName = ws.Range("C" & lRow).Value
                iIndex = 0
                iIndex = InStr(strName, "-")
                if iIndex > 0 then
                    ws.Range("D" & lRow).Value = Trim(Left(strName, iIndex - 1))
                else
                    ws.Range("D" & lRow).Value = "not found"
                end if
            End If
        End If

        lRow = lRow + 1
        ws.Range("A" & lRow).Activate
    Loop
End Sub

Per the posters comments on what is desired. Let's see if this works.

Private Sub CommandButton5_Click()
Dim rs As New ADODB.Recordset
Dim ws As Excel.Worksheet
Dim lRow As Long
Dim strCity As String
Dim strName As String
Dim iIndex As Integer

Set ws = Application.ActiveSheet

'Add fields to your recordset for storing data.  You can store sums here.
With rs
    .Fields.Append "Row", adChar, 20
    .Fields.Append "ColumnB", adChar, 70
    .Fields.Append "ColumnC", adChar, 70
    .Open
End With

lRow = 1

'Loop through and record what is in the columns we want to look at
Do While lRow <= ws.UsedRange.Rows.Count

    rs.AddNew
    rs.Fields("Row").Value = lRow
    rs.Fields("ColumnB").Value = ws.Range("B" & lRow).Value
    rs.Fields("ColumnC").Value = ws.Range("C" & lRow).Value
    rs.Update

    lRow = lRow + 1
    ws.Range("A" & lRow).Activate
Loop

If rs.EOF = False Then
    rs.MoveFirst
End If

'Now go through and check the values of the second column against what we recorded from the first
lRow = 1
Do While lRow <= ws.UsedRange.Rows.Count

    'Find the record with this order number.
    rs.Filter = ""
    rs.Filter = "ColumnB Like '%" & ws.Range("A" & lRow).Value & "%'"
    If rs.RecordCount > 0 Then
        ws.Range("D" & lRow).Value = rs.Fields("ColumnC").Value
    End If

    lRow = lRow + 1
    ws.Range("A" & lRow).Activate
Loop
End Sub