Formula to Search If/Then/Else

[SadCowDisease]

Hello all,
I've been trying to create a database for my teams new classes, and I'm running into a syntax issue.
I'm new to excel formulas, and am fresh from beginner Python 3, so it's getting a bit jumbled.
Basically, I have a sheet with raw data on tests passed, failed, and missing, and a place where I want to display who needs to submit/ resubmit what class. Data is split into cells on a back-end page, organized by columns A (class) | B-M (Passed) | N-Z (Failed) | AA-AM (Missing), displaying names of each person in each category.
So what I need is a formula that looks by row, checks the class name against passing students, and if there is a class that doesn't have a given name, then it prints the class name.
Thus far, the best I've made is one that checks class name against missing column for specific names, but I need to be able to also get failed names, and not including passing ones.
Here's what I've gotten thus far, as mentioned above:
=IFERROR(UNIQUE(FILTER('Knowledge Checks'!E:E,'Knowledge Checks'!H:H=C2)),"No Class Missing")

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I think to help, somebody would need to be able to set up a dummy set of data in the same manner that you have. From reading your description, I am not able to do that with any confidence. What about a small set of dummy data that shows your layout and any data variations, include your expected results, manually entered in the second sheet and post both with XL2BB so that we can see what you want and also copy your sample data to try to replicate your expected results?

 

[SadCowDisease]

Here's a sample of what I'm working with and an example of the goal I'm aiming for. I've also updated my profile, I'm on Windows with 365.
Thanks!

 

[Peter_SSs]

Here's a sample of what I'm working with

Unfortunately, we cannot copy from that to test, which is why I suggested XL2BB. I have typed out the data this time but many helpers will just pass your thread if you don't make it easy for them.

One way to make this nice and easy would be to add an extra column to the original data sheet like this. You can hide the column if you want.

SadCowDisease.xlsm
	A	B	C	D	E									N	O	P	Q										AA	AB	AC	AD										AN
1		Passed												Failed													Missing													All Passed
2	Class 1	Alfred	Cameron	David										Cole													Ward													|Alfred|Cameron|David|
3	Class 2	Cameron	David	Cole										Ward													Alfred													|Cameron|David|Cole|
4	Class 3	David	Cole	Ward										Alfred													Cameron													|David|Cole|Ward|
5	Class 4	Cole	Ward	Alfred										Cameron													David													|Cole|Ward|Alfred|
Sheet1

Cell Formulas
Range	Formula
AN2:AN5	AN2	="|"&TEXTJOIN("|",1,B2:M2)&"|"

Then on the other sheet you just need this formula in the cell below the first name and copy across.

SadCowDisease.xlsm
	A	B	C	D	E
1	Alfred	Cameron	David	Cole	Ward
2	Class 2	Class 3	Class 4	Class 1	Class 1
3	Class 3	Class 4			Class 2
4
Sheet2

Cell Formulas
Range	Formula
E2:E3,C2:D2,A2:B3	A2	=FILTER(Sheet1!$A2:$A5,ISERROR(SEARCH("|"&A1&"|",Sheet1!$AN2:$AN5)),"")
Dynamic array formulas.

 

[SadCowDisease]

Hello,
Thank you for your feedback! It's gotten me a lot closer to what I'm after, however I'm seeing where more examples is important. I was able to get Xl22b working, hopefully this uploads correctly. The top section is a similar version of what I'm working with along with an extra section that's the combination of the fail and missing columns for each group, the bottom is the list I'm hoping to get together, and to the right of that is a side note explaining the blue can be ignored as it's there to provide explanation of why there are gaps in the data set column.
The issue I ran into when I tried the solution you provided was that I believe it's counting the blank cells in the dataset. Is there any way to make it skip calculating cells with no names?
Thank you!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Raw Data
2	Shift	Class	Morning Pass	Morning Fail	Morning Missing	Tests Needed	Afternoon Pass	Afternoon Fail	Afternoon Missing	Tests Needed	Evening Pass	Evening Fail	Evening Missing	Tests Needed
3	Swing	Calls					CG, JP, KK, KB		AB, AR, DR, JL, JR, MA, MJ
4	Grave	Calls									CY, DR, JA, MM, SS		IF, JT, KH LW
5	Day	Calls	EH		AP, CL, DB, FP, JL, JS, KB, MK, MH, MZ, TN	AP, CL, DB, FP, JL, JS, KB, MK, MH, MZ, TN
6	Swing	Calls					AB, DR, JL, JR		AR, CG, JP, KK, KB, MA, MJ
7	Day	Calls	AP, CL, DB, FP, JS, MH, MZ	MH	EH, JL, KB, MK, TN	MH, EH, JL, KB, MK, TN
8	Grave	Calls									IF, JT, KH, LW	JT	CY, DR, JA, MM, SS
9	Swing	Calls					Mario Ariaga
10	Day	Calls	JL, MK
11	Day	Reports	CL, EH, FP, JL, MH		AP, DB, JS, KB, MK, MZ, TN	AP, DB, JS, KB, MK, MZ, TN
12	Swing	Reports					CG, DR, JP, JL, KK, KB, MA	AB	AR, JR, MJ
13	Grave	Reports									CY, JA, MM	DR	IF, JT, KH, LW, SS
14	Day	Reports	AP, DB, JS, MK, MZ, TN		CL, EH, FP, JL, KB, MH	CL, EH, FP, JL, KB, MH
15	Swing	Reports					JR	AB
16	Grave	Reports									CY, DR, IF, JT, KH, LW, SS
17	Swing	Reports					AB
18	Grave	Reports									LW
19
20	Goal: List of failed and missing classes per person											^ We can ignore blue data, it's here to show what the raw data looks like, and why there are gaps in the data set
21	EH	AP	CL	DB	FP	JS	MH	MZ	JL	MK	TN
22	Calls	Calls
23	Reports	Reports
24
25
26
27
28
29
30
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=TEXTJOIN(", ",TRUE, D3:E3)
A22:A23	A22	=UNIQUE(FILTER(B3:B18,ISERROR(SEARCH(A21,F3:F18))))
B22:B23	B22	=UNIQUE(FILTER(B3:B18,ISERROR(SEARCH(B21,F3:F18))))
Dynamic array formulas.

 

[Peter_SSs]

That is very different to what you initially presented & asked for!

the bottom is the list I'm hoping to get together

If that is the case, and it was produced by the formulas shown, what is the question/problem?

 

[SadCowDisease]

That is very different to what you initially presented & asked for!


If that is the case, and it was produced by the formulas shown, what is the question/problem?

Sorry for the poor communication! So what I have is a list of classes and names. What I'm trying to automate is names that are missing from the pass column, or names that are in the Fail/ Missing columns. At the moment, the example version should be searching for the name in both failed and missing columns, then providing the class name for the row in which it finds the name.
At this juncture, following your feedback, the issue I'm coming across is that my dataset has gaps since the data I'm getting involves utilizing some columns but not all of them. I believe with the current formulas in use, it's pulling class names where there is no name feedback at all, and I'm trying to determine how to properly search for this.

 

[Peter_SSs]

So what about some fresh sample data with the actual expected results filled in manually and explain in relation to that sample data how you manually got those results?

 

[SadCowDisease]

Here's the sheet with the Goal section filled in manually. The goal is for this section to auto-populate based on finding the searched for name in the fail or missing ranges.

Missing Class Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Raw Data
2	Shift	Class	Morning Pass	Morning Fail	Morning Missing	Tests Needed	Afternoon Pass	Afternoon Fail	Afternoon Missing	Tests Needed	Evening Pass	Evening Fail	Evening Missing	Tests Needed
3	Swing	Calls					CG, JP, KK, KB		AB, AR, DR, JL, JR, MA, MJ
4	Grave	Calls									CY, DR, JA, MM, SS		IF, JT, KH LW
5	Day	Calls	EH		AP, CL, DB, FP, JL, JS, KB, MK, MH, MZ, TN	AP, CL, DB, FP, JL, JS, KB, MK, MH, MZ, TN
6	Swing	Calls					AB, DR, JL, JR		AR, CG, JP, KK, KB, MA, MJ
7	Day	Calls	AP, CL, DB, FP, JS, MH, MZ	MH	EH, JL, KB, MK, TN	MH, EH, JL, KB, MK, TN
8	Grave	Calls									IF, JT, KH, LW	JT	CY, DR, JA, MM, SS
9	Swing	Calls					Mario Ariaga
10	Day	Calls	JL, MK
11	Day	Reports	CL, EH, FP, JL, MH		AP, DB, JS, KB, MK, MZ, TN	AP, DB, JS, KB, MK, MZ, TN
12	Swing	Reports					CG, DR, JP, JL, KK, KB, MA	AB	AR, JR, MJ
13	Grave	Reports									CY, JA, MM	DR	IF, JT, KH, LW, SS
14	Day	Reports	AP, DB, JS, MK, MZ, TN		CL, EH, FP, JL, KB, MH	CL, EH, FP, JL, KB, MH
15	Swing	Reports					JR	AB
16	Grave	Reports									CY, DR, IF, JT, KH, LW, SS
17	Swing	Reports					AB
18	Grave	Reports									LW
19
20	Goal: List of failed and missing classes per person											^ We can ignore blue data, it's here to show what the raw data looks like, and why there are gaps in the data set
21	EH	AP	CL	DB	FP	JS	MH	MZ	JL	MK	TN
22	Calls	Reports	Reports	Reports	Reports	Reports	Calls	Calls	Calls	Reports	Calls
23	Reports							Reports	Reports		Reports
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=TEXTJOIN(", ",TRUE, D3:E3)

 

[Peter_SSs]

Seems to be missing this part.

and explain in relation to that sample data how you manually got those results

For example, why is EH listed for either class since they passed Calls (cell C5) and they passed Reports (cell C11)?