Return non-unique values within a cell

[bksbksbks]

The program creating my assignment groupings has an any/all option - either match any of the criteria (these jobs or any of these companies) or match all of them (these jobs only within these companies). I have been able to get the information to almost be where I need it to be - I have gotten far enough that if a job appears in my included job list (row 4 below) more than once, I know it meets all of the criteria and thus should appear in the final list of included jobs (rows 6+). There are occasionally excluded job roles, such as B5.

Book1
	A	B	C
1	Assignment Group	Group1	Group2
2	Assigned Course	Dispensing Propane Safely	Dispensing Propane Safely
3	Any/All	All	Any
4	Includes	CUST MAINT MGR|TC HOUSEKEEPER|CUSTODIAL SUPER|TC SALES ASSOCIATE|TC DELI CLERK|CUST MAINT MGR|TC OFFICE ASSISTANT|TC ASSOCIATE MERCHANDISER|TC STORE SUPER|TC MGR|TC PARTS MANAGER HRLY|TC AST MGR|MAINTENANCE ASSOCIATE|STORE ASSOC UNDER 19|TC DELI CLERK UNDER 18|TC STOCK ASSOC UNDER 19|TC FC ASSOC UNDER 18	CUST MAINT MGR|TC HOUSEKEEPER|CUSTODIAL SUPER|TC SALES ASSOCIATE|TC DELI CLERK|CUST MAINT MGR|TC OFFICE ASSISTANT|TC ASSOCIATE MERCHANDISER|TC STORE SUPER|TC MGR|TC PARTS MANAGER HRLY|TC AST MGR|MAINTENANCE ASSOCIATE|STORE ASSOC UNDER 19|TC DELI CLERK UNDER 18|TC STOCK ASSOC UNDER 19|TC FC ASSOC UNDER 18
5	Excludes	TC AST MGR
6	All Included Jobs	CUST MAINT MGR	CUST MAINT MGR
7		CUSTODIAL SUPER
8		MAINTENANCE ASSOCIATE
9		STORE ASSOC UNDER 19
10		TC ASSOCIATE MERCHANDISER
11		TC DELI CLERK
12		TC DELI CLERK UNDER 18
13		TC FC ASSOC UNDER 18
14		TC HOUSEKEEPER
15		TC MGR
16		TC OFFICE ASSISTANT
17		TC PARTS MANAGER HRLY
18		TC SALES ASSOCIATE
19		TC STOCK ASSOC UNDER 19
20		TC STORE SUPER
Sheet1

How can I find those duplicated jobs within C4?

Current sample formula in row 6 (with many thanks to Fluff!):

=IFERROR(TRANSPOSE(SORT(UNIQUE(TEXTSPLIT(TEXTJOIN("|",,IF(AC6<>"",LET(a,TEXTSPLIT(AC4,"|"),TEXTJOIN("|",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(AC6,"|")))))),AC4)),"|"),TRUE),,,TRUE)),"")

 

[StephenCrump]

Is this what you mean?

	A	B
1
2
3
4	Includes	AA|BB|CC|DD|AA|DD|EE|EE
5	Excludes	BB|CC|DD
6	Result?	AA
7		EE
8
Sheet1

Cell Formulas
Range	Formula
B6:B7	B6	=LET(inc,TEXTSPLIT(B4,,"|"),UNIQUE(FILTER(inc,1<BYROW(inc,LAMBDA(r,SUM((inc=r)*IF(B5="",1,ISNA(MATCH(r,TEXTSPLIT(B5,,"|"),)))))))))
Dynamic array formulas.

 

[bksbksbks]

Is this what you mean?

	A	B
1
2
3
4	Includes	AA|BB|CC|DD|AA|DD|EE|EE
5	Excludes	BB|CC|DD
6	Result?	AA
7		EE
8
Sheet1

Cell Formulas
Range	Formula
B6:B7	B6	=LET(inc,TEXTSPLIT(B4,,"|"),UNIQUE(FILTER(inc,1<BYROW(inc,LAMBDA(r,SUM((inc=r)*IF(B5="",1,ISNA(MATCH(r,TEXTSPLIT(B5,,"|"),)))))))))
Dynamic array formulas.

I want to say yes. When I ran the formula in my initial cell it worked great. When I copied it to other cells on the sheet it made Excel explode, so I'm going to hope for the best

It amazes me with all I have learned about Excel that there are still so many advanced formula features that are out of my grasp. Thanks for your help!

 

[Scott Huish]

Maybe:
=UNIQUE(TOCOL(LET(f,TEXTSPLIT(B4,"|"),FILTER(f,ISNA(XMATCH(f,TEXTSPLIT(B5,"|"),0))))))

 

[StephenCrump]

Maybe:
=UNIQUE(TOCOL(LET(f,TEXTSPLIT(B4,"|"),FILTER(f,ISNA(XMATCH(f,TEXTSPLIT(B5,"|"),0))))))

The question wasn't totally clear, but I interpreted as all items appearing more than once in B4, and not in B5.

Hence for example:

	A	B
1
2
3
4	Includes	AA|AA|BB|BB|CC|DD
5	Excludes	BB
6	Result?	AA
Sheet1

Cell Formulas
Range	Formula
B6	B6	=LET(inc,TEXTSPLIT(B4,,"|"),UNIQUE(FILTER(inc,1<BYROW(inc,LAMBDA(r,SUM((inc=r)*IF(B5="",1,ISNA(MATCH(r,TEXTSPLIT(B5,,"|"),)))))))))

 

[bksbksbks]

I really do value you all of your suggestions. I have tried implementing them and have come to the realization that I may need to rethink my approach to this particular task. I hope to have an update soon!