Return to Question

I have some code which is designed to scan Columns F & G for occurrences of words found in an array, the array containing text found in Column J.

Column J contains free text from a field in SAP. The field is free text so it could be "Kerry John Pub Expenses" or "CATS O/H Kerry John", or even "CATS John Kerry O/H". There is no data entry standard for this field; this is what makes this task difficult  .

Here we have 4 rows of data, John Citizen is located in Row 3, therefore the blank cells in Columns F and G, Row 2 can be populated with his first and last name.

I have some code which is designed to scan columns F and G for occurrences of words found in an array, the array containing text found in column J.

Column J contains free text from a field in SAP. Being free text, it could be "Kerry John Pub Expenses" or "CATS O/H Kerry John", or even "CATS John Kerry O/H". There is no data entry standard for this field... and this is what makes this task difficult.

Here we have 4 rows of data, "John Citizen" is located in row 3, therefore the blank cells in columns F and G, row 2 can be populated with his first and last name.

I have some code which is designed to scan Columns F & G for occurrences of words found in an array, the array containing text found in Column J. If it finds occurrences in either Column F or Column G, it will copy and paste the terms into the corresponding columns.

During testing, the code proved not sufficient to match the outcomes which I was looking for, and the solution to this problem would be to match text in Columns F and G concurrently for two matching words rather than doing them in separate intervals.

I would like some suggestions as to how this code could be re-written to achieve this result.

Sub arraycolumnmatch()
Dim txtArray As Variant, T As Variant
Dim I As Long, J As Long

    For I = 2 To Range("E50000").End(xlUp).row
        typ = Range("F" & I).Value
        If typ = "" Then
            txt = Range("J" & I).Value
            txtArray = Split(txt, " ")

            For Each T In txtArray
                For J = 2 To Range("F50000").End(xlUp).row
                    If Range("F" & J).Value = T Then
                        match_txt = T
                        Range("F" & I).Value = match_txt
                    End If
                Next J
            Next T

            For Each T In txtArray
                For J = 2 To Range("G50000").End(xlUp).row
                    If Range("G" & J).Value = T Then
                        match_txt = T
                        Range("G" & I).Value = match_txt
                    End If
                Next J
            Next T
        End If
    Next I
End Sub

I have some code which is designed to scan Columns F & G for occurrences of words found in an array, the array containing text found in Column J. 

Column J contains free text from a field in SAP. The field is free text so it could be "Kerry John Pub Expenses" or "CATS O/H Kerry John", or even "CATS John Kerry O/H". There is no data entry standard for this field; this is what makes this task difficult .

Example

Here we have 4 rows of data, John Citizen is located in Row 3, therefore the blank cells in Columns F and G, Row 2 can be populated with his first and last name.

I would like some suggestions as to how this code could be re-written to achieve this result more efficiently

    Sub arraycolumnmatch()
    Dim txtArray As Variant, T As Variant
    Dim I As Long, J As Long

    For I = 2 To Range("E50000").End(xlUp).row
        typ = Range("F" & I).Value
    If typ = "" Then
        txt = Range("J" & I).Value
        txtArray = Split(txt, " ")

        For Each T In txtArray
            For J = 2 To Range("G50000").End(xlUp).row
                If Range("G" & J).Value = T Then
                    match_txt = T
                    Range("G" & I).Value = match_txt
                    Exit For
                End If
            Next J
        Next T

        For Each T In txtArray
            For J = 2 To Range("F50000").End(xlUp).row
                If Range("F" & J).Value = T Then
                       match_txt = T
                      
                 'Check for surnames which could match first names such as Mary Michael
                       If Not Range("G" & I).Value = T Then

                          Range("F" & I).Value = match_txt
                          Exit For
                       End If
                End If
            Next J
        Next T
   End If
Next I
End Sub