Custom sort not working correctly on larger file

[Bassie]

Hi,

Is there a way to make custom sort work correctly on larger files? I created a custom list but both in VBA and in regular excel it does not work correctly on larger files, while it does work perfectly fine on smaller files.
Is this a known issue and is there a way to resolve this?

Current VBA code:

MPListGL = "IT, DE, NL, UK, SE"
vSortlistGL = Array(MPListGL)
Application.AddCustomList ListArray:=vSortlistGL
ActiveSheet.UsedRange.sort key1:=GL, Header:=xlYes, ordercustom:=Application.CustomListCount + 1

Kind regards,
Bassie

 

[kevin9999]

How do you define a "big file"? I tested the following code on a test sheet with 100k rows x 25 columns (=2.5M cells) and it sorted by your custom order in the blink of an eye. As you haven't provided any details on data layout, I used columns A-Y, headers in row 1, sort values in column A.

Option Explicit
Sub BigSort()
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1").CurrentRegion
    
    Application.AddCustomList ListArray:=Array("IT", "DE", "NL", "UK", "SE")
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=ws.Range("A1"), _
        CustomOrder:=Application.CustomListCount
        .SetRange rng
        .Orientation = xlTopToBottom
        .Apply
    End With

End Sub

 

[DWRgt2885]

Sorting large files with a custom list can be slow and just awful, especially if the file contains a large number of rows and columns.
I ran into something just like this a while back trying to stitch together multiple files that had tracking numbers. They all came over in different ways, formats, weird characters etc. It would take someone 2-3 hours a day to "clean it up" and get it ready for import. Anyway, I built what is now a "Tracking Import" program in Excel where we could import 10 sheets with hundreds or thousands of rows of dirty data haha...So I am quite familiar with trying to sort large amounts. Try this below. I'm sure you have screen updating off already, but I plucked this out of my workbook so wanted to make sure. But would be eager to know if this work for you. I'm sure you'll need to make a few tweaks though.

Sub CustomSort()

    ' Turn off screen updating
    Application.ScreenUpdating = False
    
    ' Define custom sort list
    Dim MPListGL As String
    MPListGL = "IT, DE, NL, UK, SE"
    Dim vSortlistGL As Variant
    vSortlistGL = Array(MPListGL)
    
    ' Add custom list to Excel
    Application.AddCustomList ListArray:=vSortlistGL
    
    ' Get data range
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Dim DataRange As Range
    Set DataRange = ActiveSheet.Range("A1", "C" & LastRow)
    
    ' Sort data using custom list
    With DataRange
        .Sort Key1:=Range("GL1"), Header:=xlYes, OrderCustom:=xlSortNormal
    End With
    
    ' Turn on screen updating
    Application.ScreenUpdating = True
    
End Sub

 

[Bassie]

How do you define a "big file"? I tested the following code on a test sheet with 100k rows x 25 columns (=2.5M cells) and it sorted by your custom order in the blink of an eye. As you haven't provided any details on data layout, I used columns A-Y, headers in row 1, sort values in column A.

Option Explicit
Sub BigSort()
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1").CurrentRegion
   
    Application.AddCustomList ListArray:=Array("IT", "DE", "NL", "UK", "SE")
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=ws.Range("A1"), _
        CustomOrder:=Application.CustomListCount
        .SetRange rng
        .Orientation = xlTopToBottom
        .Apply
    End With

End Sub

Hi Sorry for the late reply!

with "A big file" I mean about 90 columns and 1milion rows.

your code did indeed work for smaller files but not for my big file. Also if i try to sort without VBA (by going to custom sort > custom list) it still does not work properly.

The problem I see is that the values are not the entire cell, which mean the cell is something like "DE xxxxx" or "IT xxxxx". Because if I remove all the X's the sort works fine. Is this a known issue or am I still doing something wrong you think?

Regards,
Bassie

 

[johnnyL]

What do you mean when you say not working properly? Can you provide a couple examples of what the sort is doing 'incorrectly' & what you are expecting the sort to do?

 

[johnnyL]

You might want to add some more details so we can figure out an approach that will work for you.

1) You mentioned "about 90 columns and 1million rows". Are all those columns to be sorted separately from the other columns or if one column changes order of data the rest of the columns should reflect the same change in order?

2)) Are all of the columns the same length in rows?

3) Do all of the columns have the same data for the most part, but in different order?

4) You mentioned "DE xxxxx or IT xxxxx". Is all the data going to start with two letters followed by a space followed by 5 more characters?

5) What are the 'xxxxx'? Are they numbers or letters or a combination of both?

Please provide a sample that fairly represents what your data may look like, what it looks like after you sort it, and what you are wanting it to look like after the sort.

 

[kevin9999]

Could you provide a small sample of your data using the XL2BB add-in?

 

[kevin9999]

Try out the following on a copy of your file. Assumes column A holds the values to sort on. It inserts a column in B:B, evaluates the first 2 characters from column A, sorts on column B, then deletes column B. I tested it on data with 1M rows & 90 columns. It took a while (30 seconds+) but it did seem to work.

EDITED

Option Explicit
Sub Bassie()
    Application.ScreenUpdating = False
    Dim t As Double: t = Timer
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("Sheet1")   '<~~ change to actual sheet name
    
    Dim LRow As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    
    With ws.Range("B1")
        .EntireColumn.Insert
        .Value = "temp"
    End With
    
    With Range("B2:B" & LRow)
        .Value = Evaluate("Left(" & .Offset(, -1).Address(, , , 1) & ",2)")
    End With
    
        Set rng = ws.Range("A1").CurrentRegion
        Application.AddCustomList ListArray:=Array("IT", "DE", "NL", "UK", "SE")
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=ws.Range("B1"), _
            CustomOrder:=Application.CustomListCount
            .SetRange rng
            .Orientation = xlTopToBottom
            .Apply
        End With
    ws.Range("B1").EntireColumn.Delete
    
    MsgBox Timer - t
    Application.ScreenUpdating = True
End Sub

 

[johnnyL]

@kelvin9999, LOL, 30 seconds to sort just one column?

 

[kevin9999]

@kelvin9999, LOL, 30 seconds to sort just one column?

I actually sorted the entire range using that as the sort column (as you'll see from the code) which was 91M cells - the actual file was over 250MB saved in binary format...