I'm trying to sort a column by a custom list that is in no specific order.
Example:
This is how data starts:
This is how data SHOULD be after sort:
This is how data currently gets sorted by below code:
Code:
var ws = context.workbook.worksheets.getActiveWorksheet();
ws.getRange("A1").values = "Col_Header"
ws.getRange("A2").values = "D"
ws.getRange("A3").values = "Z"
ws.getRange("A4").values = "C"
ws.getRange("A5").values = "B"
await context.sync()
var Used_Rng_And_Props = ws.getUsedRange(true)
Used_Rng_And_Props.load(['rowCount', 'columnCount'])
await context.sync()
var sort_rng = ws.getRangeByIndexes(1, 0, Used_Rng_And_Props.rowCount - 1, Used_Rng_And_Props.columnCount);
sort_rng.select()
var sort_arr = ['C', 'B', 'Z']
var col_index = 0
// Sort the range
const sortFields = [
{
key: col_index,
//ascending: false, //true = Sort alphanumeric which defeats purpose
sortOn: Excel.SortOn.value, //value or values seem to work, not sure which is correct, both procude same result.
values: sort_arr,
}
];
sort_rng.sort.apply(sortFields);
await context.sync()
Update:
This is how you would do it in VBA:
Sub runSortC()
Dim vCustom_Sort As Variant, rr As Long
vCustom_Sort = Array("C", "B", "Z")
Application.AddCustomList ListArray:=vCustom_Sort
With Range("A:A")
.Parent.Sort.SortFields.Clear
'sort on custom order with header
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, _
OrderCustom:=Application.CustomListCount + 1
.Parent.Sort.SortFields.Clear
End With
End Sub
This is how you would do it via Excel UI:
