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.

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.

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

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

Then choose the Custom List under Order.

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)

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

Item	The text that you want to sort

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

 

Access Excel assign number for custom sorting sequence