Sorting Columns by Sum of Values

[williamburge]

Hello friends, I am looking for help in sorting the fields in my data set. My data is structured as follows:

Company	Category 1	Category 2	Category 3	Category 4
Product 1	3	4	1	5
Product 2	1	4	2	14
Product 3	1	2	4	20
Product 4	2	5	5	19

I would like to reorder the fields such that they are ordered by the sum of the values for all rows. In this example, Cat1 = 7, Cat2 = 15, Cat3 = 12, Cat4 = 68. This means the new order should be Cat4, Cat2, Cat3, Cat 1, since that puts them in descending order by sum of columns (keeping the Company field in place). My new table would like like:

Company	Category 4	Category 2	Category 3	Category 1
Product 1	5	4	1	3
Product 2	14	4	2	1
Product 3	20	2	4	1
Product 4	19	5	5	2

I know that I can make these changes manually using the select tool, but is there any way to automate the process? My data has a large # of Categories and Products, so a manual solution isn't practical. I've previously been using Excel on the output to make these changes, but would prefer a native Alteryx solution

[CharlieS]

@williamburge

A solution is attached. Let me know if you have any questions.

[patrick_mcauliffe · Accepted solution]

@williamburge

The easiest way in my opinion would be to sort the columns in the order you want them, and then feed that into a cross tab.

See attached.

Edit: looks like @CharlieS beat me to the submit button. That's a good one too.

[williamburge]

This is great - really appreciate the help. To take my question a step further, how would you go about converting this into a macro so that I have an easily replicable solution? The tricky part for me is that the # of columns/rows is variable depending on the data, and I don't know how to set up the macro input that feeds into the first transpose to account for this.

Thanks!

[williamburge]

Answered my own question, solution is attached if anyone needs it