CODEWITHSUNDEEP
vbaexcel
Adrift
Adrift

Reputation: 59769

Checking if a cell value exists in another cell

Sorry for the misleading title, hopefully my explanation helps you understand what I want.

I have three columns:

   A             B                  C
  SKU      media_gallery       image_paths
LNH222A                    +/JPEG/LNH222A-5.jpg
LNH222B                    +/JPEG/LNH222A-8-ROOM.jpg
                           +/JPEG/LNH222B-5.jpg
                           +/JPEG/LNH222B-6R.jpg 
                                  ....

I want to check if a cell's value within column A exists somehwere in a cell's value of column C, and if so put the matching column C cell into column B parallel to the matching string. So if LNH222A exists somewhere in column C, take that matched cell value and place it into column B.

So in the example above, cell B2 should have the value of:

+/JPEG/LNH222A-5.jpg+/JPEG/LNH222A-8-ROOM.jpg

The same would happen for LNH222B and so on ..

Upvotes: 0

Views: 616

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This assumes that your data begins in row #2:

Sub Adrift()
    Dim NA As Long, NC As Long, v As String, I As Long, J As Long
    Dim v2 As String
    NA = Cells(Rows.Count, "A").End(xlUp).Row
    NC = Cells(Rows.Count, "C").End(xlUp).Row
    For I = 2 To NA
        v = Cells(I, "A").Value
        v2 = ""
        For J = 2 To NC
            If InStr(Cells(J, "C").Value, v) > 0 Then
                v2 = v2 & ";" & Cells(J, "C").Value
            End If
        Next J
        Cells(I, "A").Offset(0, 1).Value = Mid(v2,2)
    Next I
End Sub

Upvotes: 1

Related Questions