Rearrange A Delimited Column Of Data In To Separate Rows With Fixed Number Of Columns

[Excel Fox]

Just realized that you wanted the output in separate columns. The above keeps the output in a single column. The below splits it in to two columns

Sub Consolidator()

    Dim var As Variant, varOut As Variant
    Dim lng As Long
    Dim lngRows As Long
    Dim lngIndex As Long
    Dim lngSplit As Long
    var = Range("A1").CurrentRegion.Resize(, 2).Value2
    For lngRows = LBound(var) To UBound(var)
        lng = lng + CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
    Next lngRows
    ReDim varOut(1 To lng, 1 To 3)
    lngIndex = 1
    For lngRows = LBound(var) To UBound(var)
        lng = CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
        For lng = lngIndex To lngIndex + lng - 1
            lngSplit = lngSplit + 1
            varOut(lng, 1) = var(lngRows, UBound(var, 2) - 1)
            varOut(lng, 2) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 2)
            varOut(lng, 3) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 1)
        Next lng
        lngIndex = lng
        lngSplit = 0
    Next lngRows
    ActiveSheet.Next.Cells(1).Resize(UBound(varOut), 3).Value = varOut
    
End Sub