Exclude values from delineated list

[bksbksbks]

Good morning, forum. Long-time lurker, first-time poster here. Every month I get a report of jobs that are assigned to various groups. In that report the data comes in as either included or excluded from a grouping, as shown below:

	Sample Group 1	Sample Group 2
Includes	Job1, Job2, Job3, Job4, Job5	Job3, Job4, Job5, Job 6
Excludes	Job3, Job 5	Job 5

My goal is to take that data and report out only the jobs that are included, as shown in the added row:

	Sample Group 1	Sample Group 2
Includes	Job1, Job2, Job3, Job4, Job5	Job3, Job4, Job5, Job 6
Excludes	Job3, Job 5	Job 5
Result	Job1, Job2, Job4	Job3, Job4, Job 6

As of now I have figured out how to achieve this by listing out each value in row 2 in its own cell, then each value in row 3, and using match() to compare each job individually, but I am hoping to find a way to achieve this all in one cell in Office 365.

Thanks for all of the tips and tricks I have silently gleaned over the years!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C
1		Sample Group 1	Sample Group 2
2	Includes	Job1, Job2, Job3, Job4, Job5	Job3, Job4, Job5, Job 6
3	Excludes	Job3, Job5	Job5
4		Job1, Job2, Job4	Job3, Job4, Job 6
Sheet4

Cell Formulas
Range	Formula
B4:C4	B4	=LET(a,TEXTSPLIT(B2,", "),TEXTJOIN(", ",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(B3,", "))))))

 

[bksbksbks]

Thank you so much for your quick reply, Fluff! That formula gave me a #CALC error on groups that had no exclusions, so I threw a quick workaround in and it works perfectly:

=IF(J3<>"",LET(a,TEXTSPLIT(J2,", "),TEXTJOIN(", ",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(J3,", ")))))),J2)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Cubist]

One more option that handles empty exclude list.

Book1
	A	B	C	D
1		Sample Group 1	Sample Group 2	Sample Group 3
2	Includes	Job1, Job2, Job3, Job4, Job5	Job3, Job4, Job5, Job 6	Job3, Job4, Job5, Job 6
3	Excludes	Job1, Job2	Job5
4		Job3, Job4, Job5	Job3, Job4, Job 6	Job3, Job4, Job5, Job 6
Sheet5

Cell Formulas
Range	Formula
B4:D4	B4	=LET(t,TEXTSPLIT(B2&", "&B3,", "),u,UNIQUE(t),TEXTJOIN(", ",,IF(MAP(u,LAMBDA(m,SUM(--(t=m))))=1,u,"")))

 

[Fluff]

Another option to handle empty Excludes

=TEXTJOIN(", ",,UNIQUE(TEXTSPLIT(B2&", "&B3,", "),1,1))

 

[Cubist]

Another option to handle empty Excludes

=TEXTJOIN(", ",,UNIQUE(TEXTSPLIT(B2&", "&B3,", "),1,1))

Forgot about the [exact once] parameter of UNIQUE.

 

[Fluff]

So did I originally, hence the use of isna/xmatch.