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.
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)
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