Aligning two columns with partial matches in EXCEL

问题:

Hey guys I am trying to align two columns that have the same partial values (say, up to the 9 first characters). Essentially, some emails in the second column show up without being in the first column. I would like to know how to match the first column emails to the second column. Thank you example

回答1:

I would like to know how to match the first column emails to the second column.

Try,

=match(a2&"*", b:b, 0)

回答2:

I'm sure you can do this with an Excel formula, but in the meantime, here is a VBA solution. Press ALT + F11 to open the VBA Editor, on the left hand pane locate your Excel file, and under that double-click on "This Workbook". Now paste this code in:

    Option Explicit

Private Sub Match()

    Dim i As Long
    Dim sourceCell As String

    'Start at 2 if you have a header on row #1
    For i = 2 To ActiveSheet.UsedRange.Rows.Count

        '1 is the A column
        sourceCell = Cells(i, 1)

        '2 is the B column
        If Trim(sourceCell) = Left(Trim(Cells(i, 2)), Len(sourceCell)) Then

            Cells(i, 3).Value = "Match"

        End If

    Next i

End Sub

Now make sure that you have the correct Excel sheet open and again from the VBA editor click on Run -> Run Sub/User form.

CAUTION - It will report results and overwrite any data in your "C" column. If you need to change this, just change the Cells(i, 3) value from 3 to another column.