Search comma separated values in the next cell

Hi All,

I am looking for to solve a problem where I need to search for a each value in a cell which has comma separated values and check if it is available in another cell.

Ex. Cell A1 has A,B,C
Cell B1 has B,C,X

Cell C1 = Yes ( As Cell B1 because we can find B & C )

If any values from the comma separated from A1 is present in B1 , I am trying get a Yes result in C1.

Any ideas ? Would help a lot.

Thanks In advance.

It would be easier to help and test possible solutions if you could attach a copy of your file which contains a little more data. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

Hi @avmax
Try this:

Sub myTest()
Dim X
Dim Y
Dim LRow As Long
Dim i As Integer, j As Integer, k As Integer
Dim Z As Boolean
LRow = Cells(Rows.count, 2).End(xlUp).Row
For k = 2 To LRow
    Z = False
    X = Split(Cells(k, 2), ",")
    Y = Split(Cells(k, 3), ",")
    For i = LBound(X) To UBound(X)
        For j = LBound(Y) To UBound(Y)
            If X(i) = Y(j) Then
                Z = True
                Cells(k, 4).Value = "Yes"
                GoTo GoNext
            End If
        Next j
    Next i
GoNext:
If Z = False Then Cells(k, 4).Value = "No"
Next k
End Sub