Access Excel assign number for custom sorting sequence

This Access Excel tutorial explains how to assign number to Text for custom sorting sequence and how to use Excel Custom Lists.

You may also want to read:

Sort data using custom Function by Bubble Sort

Sort data using Range Sort Method

Sort data using Table AutoFilter

Sort data using Worksheet AutoFilter

Access Excel Assign number to Text for custom sorting sequence

Suppose your company has departments IT, HR, Finance. Normally if you sort the sequence in ascending order, it would become

Finance > HR > IT

What if we need to sort by Finance > IT > HR? Apparently you cannot do that with descending order or ascending order, it is a custom order defined by yourself.

Excel Custom Lists – custom sort Text in desired order

Excel has a built-in Function called Custom Lists, in which you can define the sorting order.

In Excel 2013, navigate to File > Options > Advanced > click on Edit Custom Lists button.

custom_list

In List entries box, type the desired order, then press Add button.

custom_list_04

Now when you do sorting in worksheet, you can click on Data tab > Sort

Then choose the Custom List under Order.

custom_list_05

Access Excel Custom VBA Function – assign number to Text for custom sorting sequence

Instead of sorting using Custom Lists, I prefer to assign a numerical value for each Text, it is more flexible when dealing with ascending order / descending order.

I was inspired to make this Function because I needed a column to do sorting in Access Report, but Access does not have a Custom List as Excel does.

VBA code of custom Access Excel Function – assign number to Text for custom sorting sequence

Define your list of items in ascending order in line four of the code.

Public Function wCustomSort(Item) As Integer
    Dim arrayList As Variant
    arrayList = Array("Finance", "HR", "IT"'put your items here in desired order
    wCustomSort = 1000  'if the item is not defined in arrayList, assign a default order as 1000
    For i = LBound(arrayList) To UBound(arrayList)
        If arrayList(i) = Item Then
            wCustomSort = i
        End If
    Next i
End Function

Syntax of custom Access Excel Function – assign number to Text for custom sorting sequence

wCustomSort(Item)
Item The text that you want to sort

This Function returns the order as defined in line four of the code, the first item in the list is assigned 0. Therefore “Finance” is assigned 0, “HR” is 1, “IT ” is 2. If Item is not found in the defined list, the Function returns 1000.

Example of custom Access Excel Function – assign number to Text for custom sorting sequence

Formula Result
=wCustomSort(“Finance”) 0
=wCustomSort(“HR”) 1
=wCustomSort(“IT”) 2
=wCustomSort(“testing”) 1000

Outbound References

http://www.mrexcel.com/forum/microsoft-access/256088-custom-sort-order-access.html

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *