Training Results and Ranking

[littlefish]

I have a table which ranks employee training for: Excel, Word, PowerPoint, and Outlook.

Column A - has the employee completed training for all four Office programs? Y/N
Col B - employee name
Col C-F - ranks 1 through 4 each Office program according to employee proficiency (E, W, P, O)

I'm looking for two results tables for each Office training program:

Table 1 which shows employees who have had training (sorted by rank, then name) and whether or not they have completed all training.

Table 2 with names who have not had training in the specific Office program.

(These tables will update as training is completed.)

All input is welcome and very much appreciated. -LF

 

[lousymc]

Paste the name in column I, in column J2 put this and copy down:
=IFERROR(INDEX($C$1:$F$1,1,COUNTA(C2:F2)),"R0")

put this in K2 copy down:
=IF(J2="R4","Y","N")

put this in N2 enter with control + shift and copy down: (its messy, idk a simpler way)
=IFERROR(INDEX($I$2:$I$6,SMALL(IF($J$2:$J$6="R0",ROW(INDIRECT("1:"&COUNTA($I$2:$I$6)))),ROW($I$2)-ROW(I2)+1)),"")