Need help with array formula

[demonfootball21]

I am looking for some on an array formula.

I am running three sheets. "Failing_Kids!"... "Final!"..."students!"

In my "Students!" tab, I have a master list of every student. This tab remails untouched.

In my "Failing_Kids!" is where my information is uploaded to.

This has a list of every kid who is failing a class. Each row is one class

Ex -
A1)102321 B1)John C1) Smith D1)Biology
A2)102321 B2)John C2) Smith D2)math
A3)102444 B3)Bill C3)Stevens D3)English

I can not change how this is populated. I am emailed this sheet the way it is.

In my "Final!" sheet i have headings for each subject. "Math Failure", "English Failure" etc. I want it to search by ID number and put an "X" if it reads a certain class in that box. Here is what I have and it works perfect but I have one hiccup.

These are my formulas in the worksheet "Final!"

Column M =Failing_Kids!A4

A -=VLOOKUP($M4,Failing_Kids!$A$2:C500, COLUMNS(Students!A4:A4)+1,0)
B = VLOOKUP($M4,Failing_Kids!$A$2:D500, COLUMNS(Failing_Kids!A4:B4)+1,0)

Works perfect. Column M reads the ID number from Failing Kids. Column A and B reads the ID number in Column M and Searches the STUDENTS tab for their name.

Columns C - L look like this(with different headings)
C)"ENGLISH FAILURES=IF(ISERROR(SEARCH("*English*",Failing_Kids!E4,1)),IF(ISERROR(SEARCH("*Literacy*",Failing_Kids!E4,1)),"","X"),"X")
D)="MATH FAILURES" =IF(ISERROR(SEARCH("*Geometry*",Failing_Kids!$E5,1)),IF(ISERROR(SEARCH("*Math*",Failing_Kids!$E5,1)),IF(ISERROR(SEARCH("*Calc*",Failing_Kids!$E5,1)),IF(ISERROR(SEARCH("*Alg*",Failing_Kids!$E5,1)),"","X"),"X"),"X"),"X")

Since all classes are not called "Math" i have to make multiple entries.

If the kid is only failing 1 class every thing is awesome However if one kid is failing more than one class I run into a problem.
If John Smith is failing 3 classes, I will get John Smith on rows 1-3 and on each an "X" in each box he is failing.

What I need is all of the "X's" to populate on one line for John Smith. So he is only on row 1 with three X's going across
Smith John X
Smith John X
Smith John X

I need

Smith John X X X
Thanks!

 

[Mister_T]

Hi

Have you tried insert column at start of failing kids sheet, then set formula in Col A to concatenate student id & subject
in your final have stydent id and under each col heading for subject look up id&subject against col A range in failings to return x if there or if isna return ""? should then work?

 

[Excelestial]

Use your first row as a "header row" and use the same headers in your source spreadsheet. Make sure they match verbatim. Tweak your formula to reference these header rows so that you get your x's all in a row.