CODEWITHSUNDEEP
excelvba
vpm
vpm

Reputation: 99

Compare all column values against individual cells in Excel

I'd like to find if any row in Column C matches any cells in Column A or B and print out 'yes' or 'no' in an adjacent cell if it does match. The match might not be exact, because an ID may be written as '12401' but the match in the column may be like 'cf[12401]', with the ID enclosed in brackets.

This is an example of what I might find in the table. The values in A and B columns originally came from another table but I'm trying to find all instances of where they might exist in the third column.

Excel Example:

enter image description here

If possible, I'd like to list the values themselves that matched in the column. But that part would be a nice extra while the other part is more important because there are around 6000 values in the middle column so it would take days by hand.

I've tried different things like this:

=IF(COUNTIF(C2,"*" & A6 & "*" ), "Yes", "No")

or 

=IF(COUNTIF(C2,"*" & Length & "*" ), "Yes", "No")

these work for individual words or cells, but trying to check all the values in that column against the cell will return no. I've tried variations of SUMPRODUCT and others that I've found, but haven't been able to get something that works for multiple values.

Is there some function in Excel that will allow me to do this? Or maybe a way in VBA?

Upvotes: 0

Views: 75

Answers (1)

JvdV
JvdV

Reputation: 75960

Here is some UDF you could use.

enter image description here

Dim MyArr As Variant, X As Double, LR As Double

Option Explicit

Public Function MatchID(RNG As Range) As String

With ActiveWorkbook.Sheets(RNG.Parent.Name)
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    MyArr = Application.Transpose(.Range(.Cells(2, 1), .Cells(LR, 1)))
    For X = LBound(MyArr) To UBound(MyArr)
        If InStr(1, RNG.Value, MyArr(X), vbTextCompare) > 0 Then
            If MatchID = "" Then
                MatchID = MyArr(X)
            Else
                MatchID = MatchID & ", " & MyArr(X)
            End If
        End If
    Next X
End With

End Function

Public Function MatchCFNAME(RNG As Range) As String

With ActiveWorkbook.Sheets(RNG.Parent.Name)
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    MyArr = Application.Transpose(.Range(.Cells(2, 2), .Cells(LR, 2)))
    For X = LBound(MyArr) To UBound(MyArr)
        If InStr(1, RNG.Value, MyArr(X), vbTextCompare) > 0 Then
            If MatchCFNAME = "" Then
                MatchCFNAME = MyArr(X)
            Else
                MatchCFNAME = MatchCFNAME & ", " & MyArr(X)
            End If
        End If
    Next X
End With

End Function

enter image description here

In D2 Ijust used =IF(F2<>"","YES","") and dragged it sideways and down.

Upvotes: 2

Related Questions