Split and group values in excel

Question

Hi I have a column of values which has different suffix after a dot, i need it to group it based on the value after dot. Example i need to split all values that end with .pdf into one column, values with.xls as another column, etc,,,

how to do this is my doubt.

Answer 1

you can use nested dictionaries

Sub test()

    With New Scripting.Dictionary
        Dim cel As Range
            For Each cel In Range("A1").CurrentRegion
                If Not .Exists(Split(cel.Value, ".")(1)) Then .Add Split(cel.Value, ".")(1), New Scripting.Dictionary
                .Item(Split(cel.Value, ".")(1)).Add cel.Value, 1
            Next
        
            Dim iK As Long
                For iK = 0 To .Count - 1
                    Range("C1").Offset(, iK).Resize(.Items(iK).Count).Value = Application.Transpose(.Items(iK).Keys)
                Next
    End With
    
End Sub

just add reference to "Microsoft Scripting Runtime" library

Answer 2

variant using scripting.dictionary:

Sub test()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.comparemode = vbTextCompare
    
    Dim cl As Range
    
    For Each cl In [A1:A6]
        If Not dic.exists(Split(cl.Value, ".")(1)) Then
            dic.Add Split(cl.Value, ".")(1), cl.Value
        Else
            dic(Split(cl.Value, ".")(1)) = dic(Split(cl.Value, ".")(1)) & "|" & cl.Value
        End If
    Next cl
    
    Dim x%, i%, dKey, sVal
    
    x = 3
    For Each dKey In dic
        i = 1
        For Each sVal In Split(dic(dKey), "|")
            Cells(i, x).Value = sVal
            i = i + 1
        Next sVal
        x = x + 1
    Next dKey
        
End Sub

demo: