Matching codes between two columns

[smalik]

I have a number of codes in two columns A & B as shown below:

I am trying to find out which codes from Column B are not in columns A.

Column A has only one code per cell. However, certain cells in Column B has multiple codes separated by a ","
There are 500 rows in Column A so this means I have 500 codes in Column A. Column B has 850+ rows but certain rows has more than one code so there could be well over 1,000 codes

I was thinking about using Text to Column feature in Column B and then do a lookup but is there a formula I can use to identify the missing codes without going through that exercise.

Any help is greatly appreciate.

 

[Fluff]

Can you post some sample date.
Also do you have the LET function?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JamesCanale]

Maybe this bit will be useful here...

MrExcelPlayground.xlsm
	A	B	C	D	E
2	A	B	B converted to one list	In B but not in A	Or
3	a	b	b, e, f, i, l, p, g, a, k, i, m, n, o, p, s, v, y	g, k, o, s, v, y
4	b	e, f			g
5	c	i			k
6	d	l, p			o
7	e	g			s
8	f	a, k			v
9	h	i			y
10	i	m, n
11	j	o, p
12	l	s
13	m	v, y
14	n
15	p
16	q
17	r
18	t
19	u
20	w
21	x
22	z
Sheet31

Cell Formulas
Range	Formula
C3	C3	=TEXTJOIN(", ",TRUE,B3:B13)
D3	D3	=TEXTJOIN(", ",TRUE,IF(ISNA(MATCH(UNIQUE(TRIM(MID(SUBSTITUTE(C3,", ",REPT(" ",99)),IF(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)=1,1,(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)-1)*99),99))),A3:A22,0)),UNIQUE(TRIM(MID(SUBSTITUTE(C3,", ",REPT(" ",99)),IF(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)=1,1,(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)-1)*99),99))),""))
E3:E9	E3	=UNIQUE(IF(ISNA(MATCH(UNIQUE(TRIM(MID(SUBSTITUTE(C3,", ",REPT(" ",99)),IF(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)=1,1,(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)-1)*99),99))),A3:A22,0)),UNIQUE(TRIM(MID(SUBSTITUTE(C3,", ",REPT(" ",99)),IF(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)=1,1,(SEQUENCE(LEN(C3)-LEN(SUBSTITUTE(C3,", ",""))+1)-1)*99),99))),""))
Dynamic array formulas.

 

[Fluff]

@JamesCanale That will almost certainly fail with the strings that the OP has, due to the varying lengths of them.

 

[smalik]

I know it is complicated. Some of these strings are way too long. I noticed it when <CTRL F> did not work for some of these big strings.

I got it done using the Text to Column function plus Trim function plus a little bit of manual work. It was tedious but it worked.

Thanks to JamesCanale & Fluff for looking into this request. It was much appreciated.

​

 

[Fluff]

Glad you sorted it & thanks for the feedback.