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

[Hawks18]

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!

 

[Fluff]

You could create a helper column which changes the ,s to |s (or any other symbol not in your data) like so
[TABLE="width: 100"]
<tbody>[TR]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD]C| LW[/TD]
[/TR]
[TR]
[TD]C| RW[/TD]
[/TR]
[TR]
[TD]C| LW| RW[/TD]
[/TR]
[TR]
[TD]LW[/TD]
[/TR]
[TR]
[TD]LW| RW[/TD]
[/TR]
[TR]
[TD]RW[/TD]
[/TR]
[TR]
[TD]D[/TD]
[/TR]
[TR]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]

& then sort on that

 

[jmacleary]

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.

 

[Hawks18]

You could create a helper column which changes the ,s to |s (or any other symbol not in your data) like so
[TABLE="width: 100"]
<tbody>[TR]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD]C| LW[/TD]
[/TR]
[TR]
[TD]C| RW[/TD]
[/TR]
[TR]
[TD]C| LW| RW[/TD]
[/TR]
[TR]
[TD]LW[/TD]
[/TR]
[TR]
[TD]LW| RW[/TD]
[/TR]
[TR]
[TD]RW[/TD]
[/TR]
[TR]
[TD]D[/TD]
[/TR]
[TR]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Hawks18]

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.

 

[Hawks18]

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?

 

[Fluff]

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

Not that I'm aware of.

 

[Hawks18]

Not that I'm aware of.

I hate this wonderful software, lol.

 

[jmacleary]

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?

 

[mikerickson]

You could use your own sorting routine.

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

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.