Custom List for Sort Order - Using Sorted Values w/ Commas

Hawks18

New Member
Joined
Apr 11, 2016
Messages
17
So, I've got a unique scenario that I can't figure out and doesn't seem to have been solved elsewhere yet.

Excel allows users to create "Custom List" data for sorting cells - unfortunately, these lists are inherently delimited via commas and it seems to be non-negotiable.

Ordinarily, it wouldn't cause an issue except that in this case the very list that I'd like to use for Sort Order has several values that are separated by commas...

Does anybody know how-to prevent Excel from delimiting Custom List values that are listed with commas? (w/o using VBA code)

Example List:

C
C, LW
C, RW
C, LW, RW
LW
LW, RW
RW
D
G

I'd appreciate any help on this subject. Thanks! :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You could create a helper column which changes the ,s to |s (or any other symbol not in your data) like so
C
C| LW
C| RW
C| LW| RW
LW
LW| RW
RW
D
G

<tbody>
</tbody>

& then sort on that
 
Last edited:
Upvote 0
I'm not expert in using custom lists, but a quick test seems to show you are right. One way round (if you can add a helper column) would be to create your list in 1 column, and a sequence number alongside each one. Then in your helper column do a vlookup of the cells you are wanting to sort by to return the sequence number, then sort on that number.
 
Upvote 0
You could create a helper column which changes the ,s to |s (or any other symbol not in your data) like so
C
C| LW
C| RW
C| LW| RW
LW
LW| RW
RW
D
G

<tbody>
</tbody>

& then sort on that


Thanks for the response, Fluff!

It's slightly more complicated than that, as I tried to simplify my original post for conciseness...I've already created a helper column containing the appropriate sort order, but data is already transposed several times before reaching its end point that needs to be sorted correctly.

I'm really trying to avoid another level of indirection or unnecessary conversion, as to ensure our output formatting matches data inputs. Cohesion between source data and end result is important to me...
 
Upvote 0
I'm not expert in using custom lists, but a quick test seems to show you are right. One way round (if you can add a helper column) would be to create your list in 1 column, and a sequence number alongside each one. Then in your helper column do a vlookup of the cells you are wanting to sort by to return the sequence number, then sort on that number.

I'm able to perform these steps manually per query, but that process would need to be duplicated 10 times (as 10 different sheets reference and must be sorted by these list options)...it's not really practical to maintain that much redundancy.
 
Upvote 0
Basically, I am extracting data from an external sports website that contains player data, including Position Eligibility...that field may contain multiple positions, which are delimited by commas and imported as such into our data.

So, that raw data is separated into multiple columns - one of which being "Position Eligibility" that retains potentially delimited information. It gets super complex from there...as that table feeds several (15+) other sheets using Power Query and Pivot Tables, which are automatically populated, formatted, and sorted upon refreshing connections.

I'm trying to avoid manual sorting or maintenance of conversion on a per-query basis, while also retaining congruent formatting as used by our source data. 20+ queries extract data from that original table and should appear seamless relative to the source website.

C
LW
RW
D
G

^--- All of those entries sort perfectly, but the following entries are ALWAYS sorted last...

C, LW
C, RW
C, LW, RW
LW, RW

I've gotta believe there's some way to modify Excel's built-in delimiter for Custom List entries, no?

...or at the very list some special character / command to force an exception?
 
Upvote 0
I'm able to perform these steps manually per query, but that process would need to be duplicated 10 times (as 10 different sheets reference and must be sorted by these list options)...it's not really practical to maintain that much redundancy.

What about recording/writing (depending on your vba skills) a macro to do those 10 steps?
 
Upvote 0
You could use your own sorting routine.
Code:
Sub CustomListSort(RangeToSort As Range, KeyColumn As Long, arrSortOrder As Variant)
    Dim arrRaw As Variant
    Dim arrCooked As Variant
    Dim arrRows() As Long, rowCount As Long, colCount As Long
    Dim i As Long, j As Long, temp As Long
    With RangeToSort
        arrRaw = .Value
        arrCooked = .Value
        rowCount = .Rows.Count
        colCount = .Columns.Count
    End With
    
    ReDim arrRows(1 To rowCount)
    For i = 1 To rowCount: arrRows(i) = i: Next i
    
    For i = 1 To rowCount - 1
        For j = i + 1 To rowCount
            If LT(arrRaw(arrRows(j), KeyColumn), arrRaw(arrRows(i), KeyColumn), arrSortOrder) Then
                temp = arrRows(i)
                arrRows(i) = arrRows(j)
                arrRows(j) = temp
            End If
        Next j
    Next i
    
    For i = 1 To rowCount
        For j = 1 To colCount
            arrCooked(i, j) = arrRaw(arrRows(i), j)
        Next j
    Next i
    
    RangeToSort.Offset(0, 10).Value = arrCooked
End Sub


Function LT(a As Variant, b As Variant, ByRef SortOrder As Variant) As Boolean
        Dim aIndex As Variant
        Dim bIndex As Variant
        aIndex = Application.Match(a, SortOrder)
        bIndex = Application.Match(b, SortOrder)
        If IsNumeric(aIndex) And IsNumeric(bIndex) Then
            LT = aIndex < bIndex
        ElseIf IsNumeric(aIndex) Then
            LT = True
        End If
End Function
called with code like

Code:
Sub Test()
    CustomListSort Range("A1:C10"), 2, Array("C", "C,D", "A", "D")
End Sub
Note that the arrSortOrder argument of the sub CustomListSort could be a single row (or column) range.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top