Sorting with exceptions

[jc364698]

Hello,

I'm trying to sort a testing schedule that routinely has more tests added to it. The workbook has a macro that pulls the information for each test from another sheet and writes them to the table I am trying to formal. The table may appear as such, before sorting:

Machine 1 or 2 indicates that the test in currently running on the specified machine. This is how the tests should be sorted:

First, by priority from A to Z. Then, by status (machine 1, then machine 2, then queue). Third, sort by Index from smallest to largest (only as a tiebreaker). The other columns don't matter However, no matter its priority, any tests with the status "not ready" must be sent to the bottom. With these rules, the table above should look like this:

My thoughts are that they should all be sorted based on priority, then status, then index, then pick out any tests with "not ready" status and send them to the bottom.

Thanks!

 

[JGordon11]

In Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content], // Change "Table6" to the name of your table
    tblAddPrioritySortColumn = Table.AddColumn(Source, "Custom", each if [Status] = "Not Ready" then "Z" & [Priority] else [Priority]),
    tblAddStatusSortColumn = Table.AddColumn(tblAddPrioritySortColumn, "Custom.1", each 
        if [Status] = "Machine 1" then "AMachine 1" else
        if [Status] = "Machine 2" then "BMachine 2" else
        if [Status] = "Queue" then "CQueue" else "ZNot Ready"),
    tblSort = Table.Sort(tblAddStatusSortColumn,{{"Custom", Order.Ascending}, {"Custom.1", Order.Ascending}, {"Index", Order.Ascending}}),
    tblResult = Table.RemoveColumns(tblSort,{"Custom", "Custom.1"}) 
in
    tblResult

Filter Function.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Index	Title	Material	Status	Duration (Days)	Priority		Index	Title	Material	Status	Duration (Days)	Priority
2	2048	Test1	Iron	Machine 1	4	C		3851	Test5	Steel Rod	Machine 1	7	A
3	3408	Test2	Wood	Queue	4	C		2201	Test6	Wood	Machine 2	7	A
4	2786	Test3	Wood	Queue	2	B		3551	Test8	Steel Rod	Machine 1	5	B
5	3567	Test4	Steel Rod	Machine 1	2	C		3868	Test10	Steel Rod	Machine 2	5	B
6	3851	Test5	Steel Rod	Machine 1	7	A		2786	Test3	Wood	Queue	2	B
7	2201	Test6	Wood	Machine 2	7	A		2048	Test1	Iron	Machine 1	4	C
8	3315	Test7	Wood	Machine 2	6	C		2377	Test11	Iron	Machine 1	5	C
9	3551	Test8	Steel Rod	Machine 1	5	B		3567	Test4	Steel Rod	Machine 1	2	C
10	3777	Test9	Tubing	Not Ready	4	B		3315	Test7	Wood	Machine 2	6	C
11	3868	Test10	Steel Rod	Machine 2	5	B		3408	Test2	Wood	Queue	4	C
12	2377	Test11	Iron	Machine 1	5	C		3777	Test9	Tubing	Not Ready	4	B
Sheet6

 

[jc364698]

Thank you for the reply - this does what I need it to by sending "not ready" tests to the end of the list, but there is an issue with the sorting based on "status".

I may not have explained this thoroughly enough, but: a status of "machine 1" or "machine 2" means that the test is currently being run on that particular machine. Therefore, only one test at a time can have either of those at any time.

Additionally, any test currently being run (i.e. its status is "machine 1" or "machine 2") it should be at the top of the list no matter its priority. So, if for some reason a priority C test is being run on machine 1, it should be at the top of the list even if there is a priority A in queue.

 

[JGordon11]

see if this sorts the way you intend:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Change "Table1" to the name of your table
    tblAddPrioritySortColumn = Table.AddColumn(Source, "Custom", each if [Status] = "Not Ready" then "Z" & [Priority] else [Priority]),
    tblAddStatusSortColumn = Table.AddColumn(tblAddPrioritySortColumn, "Custom.1", each 
        if [Status] = "Machine 1" or [Status] = "Machine 2" then "A" else
        if [Status] = "Queue" then "C" else "Z"),
    tblSort = Table.Sort(tblAddStatusSortColumn,{{"Custom.1", Order.Ascending}, {"Custom", Order.Ascending}, {"Index", Order.Ascending}}),
    tblResult = Table.RemoveColumns(tblSort,{"Custom", "Custom.1"}) 
in
    tblResult

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Index	Title	Material	Status	Duration (Days)	Priority		Index	Title	Material	Status	Duration (Days)	Priority
2	2048	Test1	Iron	Machine 1	4	C		2201	Test6	Wood	Machine 2	7	A
3	3408	Test2	Wood	Queue	4	C		2048	Test1	Iron	Machine 1	4	C
4	2786	Test3	Wood	Queue	2	B		2786	Test3	Wood	Queue	2	B
5	3567	Test4	Steel Rod	Not Ready	2	C		3408	Test2	Wood	Queue	4	C
6	3851	Test5	Steel Rod	Not Ready	7	A		3851	Test5	Steel Rod	Not Ready	7	A
7	2201	Test6	Wood	Machine 2	7	A		3551	Test8	Steel Rod	Not Ready	5	B
8	3315	Test7	Wood	Not Ready	6	C		3777	Test9	Tubing	Not Ready	4	B
9	3551	Test8	Steel Rod	Not Ready	5	B		3868	Test10	Steel Rod	Not Ready	5	B
10	3777	Test9	Tubing	Not Ready	4	B		2377	Test11	Iron	Not Ready	5	C
11	3868	Test10	Steel Rod	Not Ready	5	B		3315	Test7	Wood	Not Ready	6	C
12	2377	Test11	Iron	Not Ready	5	C		3567	Test4	Steel Rod	Not Ready	2	C
Sheet1