Help with creating automatic spelling tests with excel

[MrBobtheteacher]

I want to create a spelling list on new sheet from my gradebook sheet
I want to auto populate a list of missed words for each student.

the logic is something like : if the corresponding cell in a students column contains a number (times tested on that word) but not the letter Y (representing mastery) : Then list in column on a new sheet.

Spelling Example.xlsx

[#2]

Post few sample data then show your desired output. Attach a sample excel file to your post so that we can download it.

[#3]

Thanks Harun,

I attached a link that I think is working. I've been trying to get it to work but the functions aren't very elegant.
At this point if I can get the "spelling lists" sheet to compress the empty cells I will have enough to work with for the meantime.

Ideally I was hoping for a way for a separate report sheet where I could pull down the students name from a list and have their words pop up.