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.

VBasic2008 · Answer 1 · 2023-01-21T21:52:28.490

3

Split and Group

If you have Microsoft 365, you can use the following:

=LET(FileNames,A1:A6,
    FileExtensions,TEXTAFTER(FileNames,"."),
    UniqueFileExtensions,UNIQUE(FileExtensions),
IFERROR(DROP(REDUCE("",UniqueFileExtensions,
    LAMBDA(CurrentResult,UniqueFileExtension,
    HSTACK(CurrentResult,FILTER(FileNames,FileExtensions=UniqueFileExtension)))),,1),""))

If you want to add the headers, add TOROW and VSTACK:

=LET(FileNames,A1:A6,
    FileExtensions,TEXTAFTER(FileNames,"."),
    UniqueFileExtensions,TOROW(UNIQUE(FileExtensions)),
VSTACK(UniqueFileExtensions,IFERROR(DROP(REDUCE("",UniqueFileExtensions,
    LAMBDA(CurrentResult,UniqueFileExtension,
    HSTACK(CurrentResult,FILTER(FileNames,FileExtensions=UniqueFileExtension)))),,1),"")))

Edit

As suggested by Mayukh Bhattacharya, simplified with TEXTAFTER.
Removed the redundant TOROW from the first formula.

edited Jan 21 '23 at 21:52

answered Jan 21 '23 at 17:44

VBasic2008

44,888
5
17
28

1

Sir, one other way could be, `=LET(a,TEXTBEFORE(A1:A6,"."), b,TEXTAFTER(A1:A6,"."), c,TOROW(UNIQUE(b)),VSTACK(c,DROP(IFERROR(REDUCE("",c,LAMBDA(x,y,HSTACK(x,FILTER(A1:A6,b=y)))),""),,1)))` instead of using `TEXTJOIN()` because of **characters cell limit**. or may be this `=LET(a,A1:A6,b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,".")))),1), c,TOROW(UNIQUE(TAKE(b,,-1))),VSTACK(c,DROP(IFERROR(REDUCE("",c,LAMBDA(x,y,HSTACK(x,FILTER(a,TAKE(b,,-1)=y)))),""),,1)))` – Mayukh Bhattacharya Jan 21 '23 at 21:23
1

You know I'm pretty new at this so every piece of advice is welcome. Thx for the `TEXTAFTER` reminder. BTW, in the first formula, `a` is never used, but I obviously couldn't remember how I can easily get the `b`. Will have to fix my formulas a little bit later and also after I have studied your 2nd one. It feels like whatever I do, there is an improvement looming. – VBasic2008 Jan 21 '23 at 21:40
1

Sir, good catch, I just realized now, thanks for pointing out, `a` is not needed. Nice. This really helps when we discuss helps to learn and improvise. thanks again, yup that works great. `=LAMBDA(array, LET(b,TEXTAFTER(array,"."), c,TOROW(UNIQUE(b)), VSTACK(c,DROP(IFERROR(REDUCE("",c,LAMBDA(x,y,HSTACK(x,FILTER(array,b=y)))),""),,1))))(A1:A6)` – Mayukh Bhattacharya Jan 21 '23 at 21:45
1

@MayukhBhattacharya Yeah, I learned this yesterday and how it can be used as a name. Thx anyway. – VBasic2008 Jan 21 '23 at 22:03
1

Sir, you are all good, doing great, I am also still learning, mostly learnt from the solutions posted by Jvdv Sir. No doubt about it. – Mayukh Bhattacharya Jan 21 '23 at 22:04
1

@MayukhBhattacharya Yeah, and [Scott Craner](https://stackoverflow.com/users/4851590/scott-craner), [David Leal](https://stackoverflow.com/users/6237093/david-leal) and [P.b](https://stackoverflow.com/users/12634230/p-b) and others. – VBasic2008 Jan 21 '23 at 22:11
1

Agree Sir. Absolutely =) [Jos](https://stackoverflow.com/users/17007704/jos-woolley) Sir as well =) – Mayukh Bhattacharya Jan 21 '23 at 22:13

score 1 · Answer 2 · answered Jan 21 '23 at 15:00

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:

score 1 · Accepted Answer · answered Jan 21 '23 at 17:38

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

Split and group values in excel

3 Answers3

Split and Group

Linked