2015年3月26日 星期四

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.


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)



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


ItemThe text that you want to sort










FormulaResult
=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

沒有留言:

張貼留言