No Excel, como transformar dados em linha separada por vírgulas em coluna com valor?

No Excel-2007 - somente macro (porque SPLIT(any, ","") não está presente em funções nativas)

Para o Excel-2010 instale o suplemento PowerQuery, clique dezenas de vezes e obtenha o resultado - um script M-language como este:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"ChangeType" = Table.TransformColumnTypes(Source,{{"name", type text}, {"type", type text}, {"data", type text}}),
    #"NewTable" = Table.AddColumn(#"ChangeType", "TypeTable", each Table.FromColumns({Text.SplitAny([type], ","), Text.SplitAny([data], ",")}, {"Type1", "Data1"}) ),
    #"ExpandTable" = Table.ExpandTableColumn(#"NewTable", "TypeTable", {"Type1", "Data1"}, {"Type1", "Data1"}),
    #"RemoveCSV" = Table.RemoveColumns(#"ExpandTable",{"type", "data"}),
    #"GetResult" = Table.Pivot(#"RemoveCSV", List.Distinct(#"RemoveCSV"[Type1]), "Type1", "Data1")
in
    #"GetResult"

Uma macro que tornará o mesmo trabalho muito mais longo:

Function expandTable(aSourceRange As Range) As Variant
Dim aSource As Variant
Dim i As Long, j As Long, k As Long
Dim aTypes As Variant, aDatas As Variant
Dim aResult() As String
Dim allTypes As New Collection
Rem Extract cell values from source range:
    aSource = aSourceRange.Value2
Rem First collect all unique types:
    For i = LBound(aSource, 1) + 1 To UBound(aSource, 1)
        aTypes = Split(aSource(i, 2), ",")
        For j = LBound(aTypes) To UBound(aTypes)
            Call allTypes.Add(aTypes(j), aTypes(j))
        Next j
    Next i
    i = allTypes.Count + 1
Rem Result table will be same height, but width will be name + all types
    ReDim aResult(LBound(aSource, 1) To UBound(aSource, 1), 1 To i)
Rem Fill first row - header:
    aResult(1, 1) = aSource(1, 1)
    For i = 1 To allTypes.Count
        aResult(LBound(aSource, 1), i + 1) = allTypes.Item(i)
    Next i
Rem Transfer data from source array to result array:
    For i = LBound(aSource, 1) + 1 To UBound(aSource, 1)
        aResult(i, 1) = aSource(i, 1)
        aTypes = Split(aSource(i, 2), ",")
        aDatas = Split(aSource(i, 3), ",")
Rem Equalize the sizes of arrays of Types and Data -
Rem if they have a different number of elements, an error may occur.
        ReDim Preserve aDatas(LBound(aTypes) To UBound(aTypes))
        For j = LBound(aTypes) To UBound(aTypes)
            For k = 2 To UBound(aResult, 2)
                If aResult(1, k) = aTypes(j) Then
                    aResult(i, k) = aDatas(j)
                    Exit For
                End If
            Next k
        Next j
    Next i
    expandTable = aResult
End Function

Como você pode ver, usar ferramentas modernas e atualizadas pode economizar muito tempo e esforço. Portanto, basta instalar o PowerQuery e aprender a converter dados com ele - existem muitos vídeos no YouTube que mostram como converter dados de um formulário para outro (é fácil e divertido)