I'd suggest:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion.Resize(, 10)
    
  With CreateObject("scripting.dictionary")
    For j = 2 To UBound(sn)
      c00 = Split(sn(j, 2), "-")(0) & "_" & sn(j, 3) & "_" & sn(j, 4)

      If .exists(c00) Then
        sp = .Item(c00)
      Else
        sp = Application.Index(sn, j, Array(1, 2, 3, 4, 5, 6, 7, 8))
      End If
      sp(Application.Match(sn(j, 10), Array("0000", "0004", "5001"), 0) + 5) = "'" & sn(j, 10)

      .Item(c00) = sp
    Next
       
    Sheet2.Cells(1).Resize(.Count, 8) = Application.Index(.items, 0, 0)
  End With
End Sub