CODEWITHSUNDEEP
vbaexcel
monty24
monty24

Reputation: 1

Matching elements of a column with other column in Excel VBA

I am new to VBA. I have two columns named 'Code' and 'Name' for example in a sheet named Sheet1

Spreadsheet

What I want to do is that each cell of 'Code' should be compared with every cell of 'Name' and then if some parts in sequence match then a separated column called 'Match' (as above) should display 'yes' or 'no' besides the column 'Code'. For example, first 'Wreakhavoc' in 'Code' column is checked with the 'Name' column then it can be seen that Wreakhavoc and 'Reak' have match (some parts of both words match in sequence) and then the column 'Match' display a yes as there is some match in sequence.

Upvotes: 0

Views: 449

Answers (1)

DisplayName
DisplayName

Reputation: 13386

You can try this:

Sub Main()
    Dim cell As Range, f As Range

    With Range("A3", Cells(Rows.Count, 1).End(xlUp))
        For Each cell In Range("E3", Cells(Rows.Count, 5).End(xlUp))
            Set f = .Find(what:=cell.Value2, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
            If Not f Is Nothing Then Cells(f.Row, 3).Value2 = "yes"
        Next
        If WorksheetFunction.CountBlank(.Offset(, 2)) > 0 Then .Offset(, 2).SpecialCells(xlCellTypeBlanks).Value = "No"
    End With
End Sub

where I assumed that codes are in column A (column index=1), matches in column C (column index=3) and names in column E (column index=5).

Otherwise change column names and indexes as per your needs.

Upvotes: 1

Related Questions