Excel VBA - Multiple Column Sort with Subsections

Question

I am trying to write an Excel macro in VBA to take data that is currently sorted based on date and sort it into multiple groupings.

The data set can be thought of as

this .

Basically, 4 columns with client information currently sorted by date.

I want to be able to do a multi-tiered sort that is different depending on the section. So, first all the information would be sorted A-Z based on "Section".

Then, for section 1, the information needs to be sorted by subsection (A-Z), then by date (oldest to newest).

But for section 2, the information needs to be sorted just by date (oldest to newest).

For section 3, the information needs to be sorted by client (A-Z), then Subsection (A-Z), then date (oldest to newest).

For section 4, the information needs to be sorted by subsection (A-Z), then client (A-Z), then date (oldest to newest).

With our example data that sort would end up looking like

this .

I have tried using the sort function but I am unable to figure out how to define the data. I created a VBA using Columns.sort with keys for each column, but that does not seem to allow me to sort each section differently.

Use Get & Transform aka Power Query. Group by Section; then you can sort each section according to the applicable rules. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 27, 2023 at 0:02
Please share the code (as text) that references the table (range) and sorts it by date so we can build on it. — VBasic2008
– VBasic2008, Commented Sep 27, 2023 at 0:30

taller · Accepted Answer · 2023-09-27 01:48:49Z

Sort data on "Section", then sort each section.

Sub demo()
    Dim rData As Range, arrData, arrKey
    Dim i As Long, Dic
    Dim oSht As Worksheet
    Dim sKey As String
    'Sheet6.[a1].CurrentRegion.Copy [a1]
    Set oSht = ActiveSheet
    Set rData = oSht.[a1].CurrentRegion
    rData.Sort key1:=oSht.Cells(2, 2), order1:=xlAscending, Header:=xlYes
    arrData = rData.Value
    Set Dic = CreateObject("scripting.dictionary")
    For i = 2 To UBound(arrData)
        sKey = Trim(rData(i, 2))
        If Not Dic.exists(sKey) Then
            Set Dic(sKey) = oSht.Cells(i, 1).Resize(1, 4)
        Else
            Set Dic(sKey) = Union(Dic(sKey), oSht.Cells(i, 1).Resize(1, 4))
        End If
    Next i
    arrKey = Dic.keys
    If Dic.Count = 4 Then
        Set rData = Dic(arrKey(0))
        rData.Sort key1:=rData.Cells(1, 3), order1:=xlAscending, _
            key2:=rData.Cells(1, 1), order2:=xlAscending, Header:=xlNo
        Set rData = Dic(arrKey(1))
        rData.Sort key1:=rData.Cells(1, 1), order1:=xlAscending, Header:=xlNo
        Set rData = Dic(arrKey(2))
        rData.Sort key1:=rData.Cells(1, 4), order1:=xlAscending, _
            key2:=rData.Cells(1, 3), order2:=xlAscending, _
            key3:=rData.Cells(1, 1), order3:=xlAscending, Header:=xlNo
        Set rData = Dic(arrKey(3))
        rData.Sort key1:=rData.Cells(1, 3), order1:=xlAscending, _
            key2:=rData.Cells(1, 4), order2:=xlAscending, _
            key3:=rData.Cells(1, 1), order3:=xlAscending, Header:=xlNo
    End If
End Sub

DJ. · Accepted Answer · 2023-09-27 00:05:15Z

0

I would create three new columns to hold calculated sort sub keys. Then the formulas would be like this (assuming the first row of data starts at A2:

SortSubKey1: =CHOOSE(MATCH(B2,{"Section 1","Section 2","Section 3","Section 4"},0),C2,TEXT(A2,"yyyymmdd"),D2,C2)

SortSubKey2: =CHOOSE(MATCH(B2,{"Section 1","Section 2","Section 3","Section 4"},0),TEXT(A2,"yyyymmdd"),"",C2,D2)

SortSubKey3: =CHOOSE(MATCH(B2,{"Section 1","Section 2","Section 3","Section 4"},0),"","",TEXT(A2,"yyyymmdd"),TEXT(A2,"yyyymmdd"))

Then you would get something like this which then you can use the normal multi-column sort to sort by Section, SortSubKey1, SortSubKey2, SortSubKey3:

answered Sep 27, 2023 at 0:05

DJ.

16.3k3 gold badges45 silver badges46 bronze badges

Comments

Ron Rosenfeld · Accepted Answer · 2023-09-27 10:49:35Z

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range or from within sheet
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Section", type text}, {"Sub Section", type text}, {"Client", type text}}),

/*Group by Section
    then Sort according to secion*/
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {
        {"Sorted", (t)=> 
            if t[Section]{0} = "Section 1"
                then Table.Sort(t, {{"Sub Section", Order.Ascending},{"Date",Order.Ascending}})
            else if t[Section]{0} = "Section 2"
                then Table.Sort(t,{{"Date",Order.Ascending}})
            else if t[Section]{0} = "Section 3"
                then Table.Sort(t, {{"Client", Order.Ascending},{"Sub Section", Order.Ascending},{"Date", Order.Ascending}})
            else if t[Section]{0} = "Section 4"
                then Table.Sort(t,{{"Sub Section", Order.Ascending},{"Client",Order.Ascending},{"Date",Order.Ascending}})
            else t,
        type table [Date=nullable date, Section=nullable text, Sub Section=nullable text, Client=nullable text]}}),

/*Sort by column 1 section    
    Then Remove first column and expand the subtables*/
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Section", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Section"}),
    #"Expanded Sorted" = Table.ExpandTableColumn(#"Removed Columns", "Sorted", {"Date", "Section", "Sub Section", "Client"}, {"Date", "Section", "Sub Section", "Client"})
    
in
    #"Expanded Sorted"

Source Data

Sorted

Collectives™ on Stack Overflow

Excel VBA - Multiple Column Sort with Subsections

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related