Compare delimited strings to a list

[Bequia]

Hi I have a workbook with two columns A & B.


Each cell in Column A contains delimited strings:
Example A1 contains OT6, PA3, PU4, PR2, PL8
A2 contains: AP3, AP, AP4, AP10


I would like to compare these cells in column A to a list in column B and return the answer if any of the values in A1 or A2 exists. It's a big workbook so column A has about 50 rows with delimited strings.


I'm able to do the reverse, that is, look up the values in B that exists in A however I would like to know the values in A that do not exist in B.


Thanks in advance!

 

[Fluff]

Are you looking for a formula, or is a macro acceptable?

 

[Bequia]

I'm looking for a formula but honestly whatever works...I'll take it.

 

[Fluff]

How about

Sub ChkExists()
   Dim ary As Variant
   Dim Cl As Range
   Dim i As Long, j As Long
   
   ary = Range("A1", Range("A" & Rows.Count).End(xlUp))
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(ary)
         For j = 0 To UBound(Split(ary(i, 1), ", "))
            If Not .exists(Split(ary(i, 1), ", ")(j)) Then .Add Split(ary(i, 1), ", ")(j), Nothing
         Next j
      Next i
      For Each Cl In Range("B1", Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("C1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

 

[Bequia]

I initially got a run time error here "Range("C1").Resize(.Count).Value = Application.Transpose(.keys)" however I added a column heading to both columns in the workbook and didn't receive a run-time error.

When I run the macro it only returns contents from the first cell and doesn't cycle through the list. I tested this by renaming the column headers. Do you know of a way for me to upload the workbook? That may be quicker.

Thanks!

 

[Fluff]

You cannot upload files to this site, but you can upload to a files share site such as OneDrive, DropBox, GoogleDrive. Mark for sharing & post the link to the thread

 

[Bequia]

Here is the link to the file: https://drive.google.com/file/d/1wcQJ_KzxV2xJ9x0-NLKfYmri2tizdLaC/view?usp=sharing


What I would like to do is find the data/strings in Column A that exist in Column B.

Thanks!

 

[Fluff]

In your op you said

I would like to know the values in A that do not exist in B.

now you are saying

What I would like to do is find the data/strings in Column A that exist in Column B.

Which is it?

 

[Bequia]

Sorry, the values that do not exist in B.

 

[Fluff]

In that case the code I supplied in post#4 works on your test file.
Although as you ave added a header row, you'll need to change A1 & B1 to A2 & B2