Searching anything within a cell and it exists in a column

[apexRaiden]

Good day,

I am trying to formulate a process where I can do a lookup or a search of ANYTHING within a cell and see if it exists in a column of entries.
The cell in question has a few lines of data but specifically has a line of data that could either have 1 entry or multiple entries that are separated by commas. I am trying to find a way to look for THOSE entries are within the column of data/entries to see if they match/exist. My end goal is to then say that if this does exist in column, then populate the rest of this entries details that are on another sheet. Almost as a IF TRUE THEN = 2ndSheet CELL DATA.

I know this formula doesn't work, but I hope it helps in the sense of explanation
=vlookup("*"&B2&"*",A:A,1,FALSE)

Again, I understand that this doesn't work, but I am just trying to help paint a picture of what im trying to obtain.
Is ANYTHING within THIS CELL over here in this column of entries?

Thank you!

 

[Joe4]

OK, I think I have come up with VBA code that will work regardless of whether or not you have soft carriage returns in column B.
Paste this code in a VBA module in your workbook, and run it on your data, and it should populate column C according to your specifications.

Sub PopulateColumnC()

    Dim lrA As Long
    Dim lrB As Long
    Dim rngA As Range
    Dim r As Long
    Dim str1 As String
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long
    Dim str2 As String
    
    Application.ScreenUpdating = False
    
'   Find last row with data in columns A and B
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Set range for column A
    Set rngA = Range("A2:A" & lrA)
    
'   Loop through all rows in column B starting at row 2
    For r = 2 To lrB
'       Set value in column C to FALSE, initially
        Cells(r, "C") = "FALSE"
'       Get value from column B
        str1 = Cells(r, "B")
'       Remove any carriage returns or line feeds from string
        str1 = Replace(str1, vbCr, "")
        str1 = Replace(str1, vbLf, "")
        str1 = Replace(str1, vbCrLf, "")
'       Split data at colon
        arr1 = Split(str1, ":")
'       Split data at commas
        arr2 = Split(Trim(arr1(1)), ",")
'       Loop through all values
        For i = LBound(arr2) To UBound(arr2)
'           Get individual value
            str2 = Trim(arr2(i))
'           Check to see if value is found in column 1
            If Application.WorksheetFunction.CountIf(rngA, str2) > 0 Then
'               If it is, set column C to TRUE and exit loop
                Cells(r, "C") = "TRUE"
                Exit For
            End If
        Next i
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[apexRaiden]

Great! I will run this shortly!

 

[apexRaiden]

OK, I think I have come up with VBA code that will work regardless of whether or not you have soft carriage returns in column B.
Paste this code in a VBA module in your workbook, and run it on your data, and it should populate column C according to your specifications.

Sub PopulateColumnC()

    Dim lrA As Long
    Dim lrB As Long
    Dim rngA As Range
    Dim r As Long
    Dim str1 As String
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long
    Dim str2 As String
   
    Application.ScreenUpdating = False
   
'   Find last row with data in columns A and B
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Set range for column A
    Set rngA = Range("A2:A" & lrA)
   
'   Loop through all rows in column B starting at row 2
    For r = 2 To lrB
'       Set value in column C to FALSE, initially
        Cells(r, "C") = "FALSE"
'       Get value from column B
        str1 = Cells(r, "B")
'       Remove any carriage returns or line feeds from string
        str1 = Replace(str1, vbCr, "")
        str1 = Replace(str1, vbLf, "")
        str1 = Replace(str1, vbCrLf, "")
'       Split data at colon
        arr1 = Split(str1, ":")
'       Split data at commas
        arr2 = Split(Trim(arr1(1)), ",")
'       Loop through all values
        For i = LBound(arr2) To UBound(arr2)
'           Get individual value
            str2 = Trim(arr2(i))
'           Check to see if value is found in column 1
            If Application.WorksheetFunction.CountIf(rngA, str2) > 0 Then
'               If it is, set column C to TRUE and exit loop
                Cells(r, "C") = "TRUE"
                Exit For
            End If
        Next i
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

So i ran the VBA on a macro enabled sheet and it populated the FALSE results but didnt seem to get to the "TRUE" portion of the code.

Here a more "realistic" set of data that hopefully gives something else that I'm missing? I have a feeling there are hidden characters getting in the way...

Test CISA KEV Lookup.xlsm
	A	B
1	IDs	Names
2	CVE-2022-417	OS Plugin ID: 1 CVE(s): CVE-2021-269
3	CVE-2021-22	OS Plugin ID: 16 CVE(s): CVE-2022-12
4	CVE-2021-269	OS Plugin ID: 12 CVE(s): CVE-2022-83, CVE-2022-417, CVE-2022-45
5	CVE-2021-2103	OS Plugin ID: 15 CVE(s): CVE-2022-207
6	CVE-2021-7	OS Plugin ID: 17 CVE(s): CVE-2022-38, CVE-2022-7
7	CVE-2022-2	OS Plugin ID: 11 CVE(s): CVE-2022-2
8	CVE-2022-12	OS Plugin ID: 19 CVE(s): CVE-2022-39
Sheet1