Split data into multiple columns and rows based on specific string

Try next code

Option Explicit

Sub Treat()
Const FR = 2                 '  Row where to start the display
Const WkCol = 5              '  Colun where to start the display
Const ColLst = "a,b,c,d"     '  List of items
Dim I   As Integer, LR As Integer, II As Integer, J As Integer
Dim ColArr, T, TT, TT1, TT2

    ColArr = Split(ColLst, ",")
    LR = Cells(Rows.Count, 1).End(3).Row
    II = FR
    Cells(1, WkCol).Resize(1, UBound(ColArr) + 2).EntireColumn.ClearContents
    Cells(1, WkCol) = "Id"
    Cells(1, WkCol + 1).Resize(1, UBound(ColArr) + 1) = ColArr
    For I = FR To LR
        If (Cells(I, 1) <> "") Then
            T = Split(Cells(I, 2), " ")
            For Each TT In T
                TT1 = Split(TT, ":")(0)
                TT2 = Split(TT, ":")(1)
                J = Application.WorksheetFunction.Match(TT1, ColArr, 0)
                Cells(II, WkCol) = Cells(I, 1)
                Cells(II, WkCol).Offset(0, J) = TT2
                If (J = UBound(ColArr) + 1) Then II = II + 1
            Next TT
        End If
    Next I
End Sub

What if i want to add more data: example: a,b,c,d,e,f instead of a,b,c,d

If the frame is exactly the same ( as it is in your example) you just need to complete the list of items
Use next code

Const ColLst = "a,b,c,d,e,f"     '  List of items

Did you put a comma between words
Attach a new Excel sample