Matching comma seperated values in 2 different cells and display non-duplicate values in 3rd cell

I have 2 columns of data. Column A is every value possible within a data set, broken out by comma in one cell. Column B is a list of values within that data set that need to be removed/excluded, also in one cell broken out by comma. I need to display in a 3rd column the data set values less the exclusions, again in a single cell broken out by comma. So essentially A-B=C, but with multiple data points in a single cell.

Examples in image link.

In case you cannot open link, it basically looks something like this:

Data set(A) Exclusions(B) Desired Output(C)
1,2,3,4,5 3,4 1,2,5
blue,green,pink,red green,red blue,pink

Some additional info: The data sets and the exclusions are unique for almost every line item, with thousands of line items I need to assess. Also the number of possible values within each data set, and the number of exclusions, varies for each line item. So row 1 might have 4 values in the set and 2 exclusions, but then row 2 might have 50 values in the set and 18 exclusions. BUT, the data set values and the exclusion values will always be in the same sequential order. So if my data points are numbers 1 through 5, the first cell would read as "1,2,3,4,5". And say I need to exclude 1, 3 and 5, the second cell will read as "1,3,5" not "3,5,1" or any similar non-sequential order. Yall get it.

I have tried the Remove Duplicates function, and tried to build something using +IF and +TEXTJOIN formulas, but failed miserably. Id prefer not have to use an array if possible. I cant/wont Text to Column the values, since each row has a different number of data set values and exclusion values. And the number of rows I'd have to create would be quite high for some line items.

I would greatly appreciate someone's assistance/time/expertise!


If you have Office 365 with the LET and FILTER and FILTERXML functions, you can use:

=TEXTJOIN(",",TRUE,
     LET(data,FILTERXML("<t><s>" & SUBSTITUTE(A2,",","</s><s>") & "</s></t>","//s"),
              excl,FILTERXML("<t><s>" & SUBSTITUTE("," & B2,",","</s><s>") & "</s></t>","//s"),
              FILTER(data,ISNA(MATCH(data,excl,0)))))

enter image description here

  • FILTERXML can split each list into an array of values
  • MATCH will return #N/A if a value in the data set is NOT found in the Exclusion set.
    • ISNA( will convert the NA errors to TRUE
  • FILTER will then return the data set elements which match with the TRUE output from the MATCH function