Extracting failed subjects

[Tigerexcel]

Student	Name	Result 1	Result 2	Result 3	Result 4	Result 5	Result 6
1	Tom	2020-1 ABC1000 65-C	2019-3 ABC1001 45-N	2020-1 ABC1005 70-D	2020-1 ABC2000 61-C	2020-1 ABC2003 35-N	2019-1 ABC1007 69-C
2	Mary	2020-1 ABC1000 35-N	2019-1 ABC1007 14-N	2020-1 ABC1010 75-D
3	Jack	2020-1 ABC2000 61-C	2020-1 ABC2003 35-N	2019-1 ABC1007 69-C	2019-3 ABC1015 41-N

I need to report on some failed results (indicated by N). The data is presented as above.
Desired output would be:
Tom ABC1001 N
Mary ABC1000 N, ABC1007 N
Jack ABC2003 N, ABC1015 N

Would be beneficial if the desired output could be located next to Name cell therefore column 3 and where there are multiple entries eg Mary it would be in column 3 and another new column ie column 4 but any form of listing will be okay as long as the N results are extracted from each record.

 

[QuietRiot]

Sub GetFails()
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim fails As String, Result As String
    Dim temp() As String
    
    Application.ScreenUpdating = False
    
    ' Insert Column for fails
    Columns("C:C").Insert Shift:=xlToRight
    Range("C1").Value = "Fails"
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For r = 2 To lastRow
        fails = ""
        lastCol = Cells(r, Columns.Count).End(xlToLeft).Column
        For c = 4 To lastCol
            Result = Cells(r, c).Value
            If InStr(1, Result, "-N") > 0 Then
                temp = Split(Result, " ")
                fails = fails & Trim(temp(1)) & " N, "
            End If
            If Len(fails) > 0 Then
                Range("C" & r).Value = Left(fails, Len(fails) - 2)
            End If
        Next
    Next
    
    Application.ScreenUpdating = True
    
End Sub

...

 

[Tigerexcel]

Thanks QR, that works well and is fast. Much appreciated.

I tried to solve this initially with Power Query but couldn't get it to work. If any of the PQ experts are out there, could you show me how you'd do this?

 

[Tigerexcel]

Got this far but I end with Table in the field that should contain the N records and can't progress from there.
The syntax I used is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", Int64.Type}, {"Name", type text}, {"Result 1", type text}, {"Result 2", type text}, {"Result 3", type text}, {"Result 4", type text}, {"Result 5", type text}, {"Result 6", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student", "Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "2019-1 ABC1007 14-N" or [Value] = "2019-3 ABC1015 41-N" or [Value] = "2020-1 ABC1000 35-N" or [Value] = "2020-1 ABC2003 35-N")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Student", "Name"}, {{"Count", each _, type table}})
in
    #"Grouped Rows"