Dynamic Sorting of Multiple Columns

[apatel1501]

Hi - i have the attached data sample with multiple years of sales and cost data. I need to be able to dynamically sort these columns on multiple fields.

For example: I need the output to automatically sort by Sales_P03 descending, then sales_p02, then sales_p01.  Sometimes i may have 7 or 8 sales columns and would need to sort by later periods first going back to Sales_P01 last. 

I know with this sample, the result is the same just sorting on Sales_p03. However, in the full actual dataset i'm working with, it will make a difference. The challenge is each data set will have varying years of data.

ProductNumber	Sales_P01	Sales_P02	Sales_P03	Sales_P04	Sales_P05	COGS_P01	COGS_P02	COGS_P03	COGS_P04	COGS_P05
Product 1	31700.1381	41968.1376	34042.52256	35836.10736	27910.49232	19425.50384	25832.24688	20804.88456	22060.97858	17033.61626
Product 2	25692.62454	27151.28955	26846.82429	18154.42739	17849.96213	18770.40976	22066.50431	21966.87791	14760.01415	14660.38775
Product 3	13059.75972	18942.43	22019.41845	14116.9604	17193.94885	9426.74045	12831.68268	15857.92235	9567.60307	12593.84274
Product 4	46974.14046	20392.80142	18088.99878	16677.797	14373.99436	32815.56482	17422.10962	16406.86444	13969.21662	12953.97144
Product 5	18337.34016	19015.25968	17816.1328	13361.39568	12162.2688	5848.3344	14381.35956	13993.59684	10071.17924	9683.41652
Product 6	18768.24922	18690.37235	17179.59754	14169.41136	12658.63655	4913.10944	12311.62395	11400.09534	9286.17797	8374.64936
Product 7	4581.5904	14090.98462	14377.11456	7685.39326	7971.5232	2864.29186	9763.64164	10371.09476	5326.48704	5933.94016
Product 8	8574.33216	11246.7576	11879.86644	5160.13212	5793.24096	5182.19214	6768.64779	7380.13803	3107.09276	3718.583
Product 9	18768.8412	15284.3775	10983.054	12854.6415	8553.318	11002.38805	9253.69083	6914.09943	7814.6144	5475.023

[DylanDowrick]

In your example data above, you wouldn't want to download by Sales_P05 first? If it is by Sales_P03, then what criteria would you use to say that it should be sorted by 03 and not 05?

[DylanDowrick · Accepted solution]

Hello @apatel1501, I went ahead and assumed you meant Sales_P05, then Sales_P04, and so on. I made a macro to sort just that!

[apatel1501]

Thanks! I said P03 because the data typically is like "2015, 2016, 2017, Year to Date 2016, Year to Date 2017". But i can likely drop the year to date columns and bring them back in after the sort is done and a record ID applied to keep the sort order.

I will check out the macro in the coming days and will accept it as solution if it works or follow up if i have any questions!

[apatel1501]

This works great - thanks! The sorting primarily helps me get do a product or customer ranking so i can just drop the columns i don't need, sort using this macro, apply the rank/record ID, and then join back to the dataset to get the rank/sort.

I'm not too familiar with XML or batch macros so this is helpful.  Any additional resources/links to help me further understand how you got to this solution?

[DylanDowrick]

For understanding XML (which I am mostly only aware of), you can get a start by looking at each tool's "XML View" (need to add via "Edit User Settings"). I checked out the sort tool's XML and figured if I could replace the XML of the decision piece then we could create the solution. To replace the XML in a tool you need (someone correct me if I'm wrong) a batch macro. In the upside-down question mark is the piece I've told it will update the sort tool.