I came up with:

Sub M_snb()
   sn = Sheets("Data").Cells(1).CurrentRegion
   
   sq = Split("Name_SampleCode_Medical Record_Date of Birth_Order Date_Gender_Build_SpikeIn_Location_Control Gender_QC", "_")
   c00 = "Display Name_Sample_Medical Record_Date of Birth_Order Date_Gender_Build_SpikeIn_Location_Control Gender_Quality"
   st = Split(c00, "_")
   
   ReDim sp(10, 0)
   For j = 1 To UBound(sn)
      If Left(sn(j, 1), 1) <> "#" Then Exit For
      sz = Split(sn(j, 1), "=")
      c01 = Mid(Trim(sz(0)), 2)
      If InStr(c00, c01) Then
            y = Application.Match(c01, st, 0) - 1
            sp(y, 0) = sq(y) & " =" & sz(1)
      End If
   Next
      
   Sheet2.Cells(30, 1).Resize(11) = sp
   Sheet2.Cells(41, 1).Resize(UBound(sn) - j + 1, UBound(sn, 2)) = Application.Index(sn, Evaluate("row(" & j & ":" & UBound(sn) & ")"), Array(1, 2, 3, 4, 5, 6, 7))
End Sub