Custom Excel Sorting: Sorting List Values
When it comes to working with data in Excel, sorting is one of the most fundamental and frequently used operations. By default, Excel provides basic sorting options that allow you to arrange data in ascending or descending order based on a single column. However, there might be instances where you need to sort data based on a specific list of values or with special sorting requirements, beyond Excel's built-in capabilities.
Why custom sorting in Excel?
Custom sorting helps you manage complex nested lists and maintain a consistent order that might not be available in the default sorting options. For instance, sorting names written in a non-Latin alphabet or arranging data based on a pre-defined set of categories that do not follow a standard alpha-numeric pattern.
Getting started with custom sorting
To create a custom list for sorting, follow these steps:
- Click on the
Filemenu and then click onOptions. - Select
Advancedfrom the left-hand side panel to access advanced Excel settings. - Under the
Generaltab, locate theEdit custom listsoption at the bottom and click on theGobutton next to it to proceed. - In the resulting window, you can add or edit custom list entries in two ways: directly entering values in the
List entriesbox, or importing lists from existing cells in your worksheet by clicking on theImportbutton.
Advanced custom sorting with VBA
For more sophisticated custom sorting needs in Excel, VBA (Visual Basic for Applications) can be employed to craft bespoke sorting functions. Here's an example:
Sub CustomSortList()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim sortList() As Variant
' Set the worksheet and the range to sort
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("A1:A15")
' Define the custom sort list
sortList = Array("Pu", "Tsu", "ʢu", "Wu", "Bi", "Tzi", "Ri", "Yi", "Mo", "Ta", "Za", "Rra", "ɲa")
' Perform the custom sort
rng.Sort Key1:=rng, Order1:=xlAscending, DataOption1:=xlSortNormal, Header:=xlNo, _
OrderCustom:=sortList, MatchCase:=False
End Sub
The above code snippet creates a custom subroutine for sorting list values. The sortList array hosts the custom sort order. The Sort property of the Range object handles the actual sorting procedure, utilizing the custom list created.
Custom sorting can unleash the full potential of Excel in terms of organizing and presenting data. With the versatility offered by both the basic custom-list and VBA-backed approaches, users can implement custom sort orders tailored to unique requirements, handling even the most thorny list-sorting challenges.
- Custom sorting is useful when Excel's default sorting options are insufficient for handling specific data.
- Users can establish custom lists for sorting via Excel's
Optionsmenu, or create advanced custom-sorting rules using VBA. - The presented
CustomSortListVBA subroutine might serve as a starting point for those keen on developing their own sophisticated custom-sorting routines.
References
- Excel Help Center: Sort data in a range or table
- Contextures - Excel Tips: Excel Sorting Tips
- Analysistab: Excel Custom Sort List - Guide with Examples
- O'Reilly: Excel 2019: Data Analysis and Business Modeling - Book by John Walkenbach