Comparing two comma-delimited lists and getting unique values

[edrickz101]

Hello!

A set of data was gathered where it consists of name of certifications and those who have completed such certifications. Required certifications (certs to shorten) needs to have a 'Yes' filled out . What I'm trying to figure out is returning a list of what certs that are required against on what are completed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Missing	AB	CD	EF	GH	IJ	KL	Required	Completed		Name	Completed	Name
2	Ed		Yes	Yes		Yes			AB, CD, GH	CD, AB, EF		Ed	CD	Ed	CD, AB, EF
3	Sam									CD, EF		Ed	AB	Sam	CD, EF
4	Jill									EF, AB, GH, IJ		Ed	EF	Jill	EF, AB, GH, IJ
5	Julia									AB		Sam	CD	Julia	AB
6	Tam									GH, AB, KL		Sam	EF	Tam	GH, AB, KL
7	Taro									GH, CD		Jill	EF	Taro	GH, CD
8	Pete									IJ, KL, CD, EF		Jill	AB	Pete	IJ, KL, CD, EF
9												Jill	GH
10												Jill	IJ
11												Julia	AB
12												Tam	GH
13												Tam	AB
14												Tam	KL
15												Taro	GH
16												Taro	CD
17												Pete	IJ
18												Pete	KL
19												Pete	CD
20												Pete	EF
Sheet1

Cell Formulas
Range	Formula
I2	I2	=TEXTJOIN(", ",TRUE,IF(C2="Yes",$C$1,""),IF(D2="Yes",$D$1,""),IF(E2="Yes",$E$1,""),IF(F2="Yes",$F$1,""),IF(G2="Yes",$G$1,""),IF(H2="Yes",$H$1,""))
J2:J8	J2	=VLOOKUP(A2,N:O,2,FALSE)
O2:O8	O2	=TEXTJOIN(", ",TRUE,IF(N2=$L$2:$L$20,$M$2:$M$20,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

You can see in the table that 'Ed' has already completed AB and CD, which means GH is only required for him.

Actually I've checked other posts here sharing some solutions (Link1 & Link2). So far both of the posts have great solutions especially in Link1 where user Yongle shared a VBA code to use. Their code 'UniqDup' compared uniques from list 1 against list 2, list 2 against list 1, and uniques among the two lists. However in Yongle's code is not working well if I have duplicates in both Required and Completed columns.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Missing 1	Missing 2	Missing 3	AB	CD	EF	GH	IJ	KL	Required	Completed
2	Ed		AB, CD, GH		Yes	Yes		Yes			AB, CD, GH	CD, AB, EF
Sheet1

Cell Formulas
Range	Formula
B2	B2	=UniqDup(K2,L2,1)
C2	C2	=UniqDup(K2,L2,2)
D2	D2	=UniqDup(K2,L2)
K2	K2	=TEXTJOIN(", ",TRUE,IF(E2="Yes",$E$1,""),IF(F2="Yes",$F$1,""),IF(G2="Yes",$G$1,""),IF(H2="Yes",$H$1,""),IF(I2="Yes",$I$1,""),IF(J2="Yes",$J$1,""))
L2	L2	=VLOOKUP(A2,P:Q,2,FALSE)

Here you could see AB and CD is picked up even though they are duplicates from Required and Completed. Expected result is supposed to be GH. I tried other variations of Yongle's formula but none generated the expected result.

Here's what the Yongle's formula looks like if both are unique to each other which works well. Refer to Sam's row

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Missing 1	Missing 2	Missing 3	AB	CD	EF	GH	IJ	KL	Required	Completed
2	Ed		AB, CD, GH		Yes	Yes		Yes			AB, CD, GH	CD, AB, EF
3	Sam		AB, IJ		Yes				Yes		AB, IJ	CD, EF
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=UniqDup(K2,L2,1)
C2:C3	C2	=UniqDup(K2,L2,2)
D2:D3	D2	=UniqDup(K2,L2)
K2:K3	K2	=TEXTJOIN(", ",TRUE,IF(E2="Yes",$E$1,""),IF(F2="Yes",$F$1,""),IF(G2="Yes",$G$1,""),IF(H2="Yes",$H$1,""),IF(I2="Yes",$I$1,""),IF(J2="Yes",$J$1,""))
L2:L3	L2	=VLOOKUP(A2,P:Q,2,FALSE)

To sum it up:
- 2 columns (Required vs Completed) where information is delimited by commas needs to be compared.
- Result of such comparison needs to be unique value/s from the Required column and must not contain any value from the Completed.
- Tried a VBA code writted by Yongle from this post but the code isn't working well if there are duplicate values inside the two lists.

Let me know if there are any unclarities in my question.

Thank you in advance

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you want

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Missing	AB	CD	EF	GH	IJ	KL	Required	Completed		Name	Completed
2	Ed		Yes	Yes		Yes			GH	CD, AB, EF		Ed	CD
3	Sam			Yes	Yes			yes	KL	CD, EF		Ed	AB
4	Jill									EF, AB, GH, IJ		Ed	EF
5	Julia									AB		Sam	CD
6	Tam									GH, AB, KL		Sam	EF
7	Taro									GH, CD		Jill	EF
8	Pete									IJ, KL, CD, EF		Jill	AB
9												Jill	GH
10												Jill	IJ
11												Julia	AB
12												Tam	GH
13												Tam	AB
14												Tam	KL
15												Taro	GH
16												Taro	CD
17												Pete	IJ
18												Pete	KL
19												Pete	CD
20												Pete	EF
Input

Cell Formulas
Range	Formula
I2:I3	I2	=LET(a,FILTER($C$1:$H$1,C2:H2="yes",""),TEXTJOIN(", ",,FILTER(a,ISNA(MATCH(a,FILTER($M$2:$M$20,$L$2:$L$20=A2),0)),"")))
J2:J8	J2	=TEXTJOIN(", ",TRUE,IF(A2=$L$2:$L$20,$M$2:$M$20,""))

 

[edrickz101]

Hey Fluff! Thanks for the response.
This really looks good! I will try out it.

Also while I was checking some files that I may have downloaded from other forums, I came across a VBA code but I will first use your formula. I will do some testing and will get back to you soon

 

[Fluff]

Ok

 

[edrickz101]

Hi & welcome to MrExcel.
Is this what you want

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Missing	AB	CD	EF	GH	IJ	KL	Required	Completed		Name	Completed
2	Ed		Yes	Yes		Yes			GH	CD, AB, EF		Ed	CD
3	Sam			Yes	Yes			yes	KL	CD, EF		Ed	AB
4	Jill									EF, AB, GH, IJ		Ed	EF
5	Julia									AB		Sam	CD
6	Tam									GH, AB, KL		Sam	EF
7	Taro									GH, CD		Jill	EF
8	Pete									IJ, KL, CD, EF		Jill	AB
9												Jill	GH
10												Jill	IJ
11												Julia	AB
12												Tam	GH
13												Tam	AB
14												Tam	KL
15												Taro	GH
16												Taro	CD
17												Pete	IJ
18												Pete	KL
19												Pete	CD
20												Pete	EF
Input

Cell Formulas
Range	Formula
I2:I3	I2	=LET(a,FILTER($C$1:$H$1,C2:H2="yes",""),TEXTJOIN(", ",,FILTER(a,ISNA(MATCH(a,FILTER($M$2:$M$20,$L$2:$L$20=A2),0)),"")))
J2:J8	J2	=TEXTJOIN(", ",TRUE,IF(A2=$L$2:$L$20,$M$2:$M$20,""))

I tried implementing this in the main file that I have. Unfortunately, it's not working as intended. It's capturing that has "yes" and not removing that's already Completed. I find it strange that I tried replicating the data that I have in a fresh file and it worked well but when I tried in the main file I did not work. I tried rebuilding the main file from scratch and still it did not work

Troubleshooting done:
- Made sure the completed and required are using the same names

I think the formula should still work even if it's trying to referrence a lot of columns

 

[Fluff]

Check that the codes are the same & that none have any leading/trailing spaces.

 

[edrickz101]

Check that the codes are the same & that none have any leading/trailing spaces.

Most definitely this is the case. I'll just rebuild the file from scratch, make data headers/lables 1:1, etc etc.

Thank you so much for the help!

 

[Fluff]

You're welcome & thanks for the feedback.