Column Sort Order

[BK209]

HI All

Im having challenge with column name rename and sort order.

I have below input data which that I parsed it from Json output file. I have 250+ columns and few of those column names have % special character which cannot be ignored.

Column Name	Column Values
V	100
AA	200
C	300
B	400
% E	500
V	10
AA	20
C	30
B	40
% E	50

Expected final output should be like 

V	AA	C	B	% E
100	200	300	400	500
10	20	30	40	50

First issue: Column order

I have used multi row formula to create RowID and then used cross tab to group the column headers. When I use multirow the columns are automatically sorted in alphabetical order and % columns takes precedence. 

Second issue: Column names have spaces (eg First Name ) getting replaced with "_" (First_Name_). 

Column Name	Column Values
%_E	500
%_E	50
AA_	200
AA_	20
B_	400
B_	40
C_	300
C_	30
V_	100
V_	10

I tried dynamic replace , Dynamic select, formula tool to replace each column with original column name but they getting sorted incorrectly because I have 250 columns so if I use sequence id appended to column names. My 11th column comes second instead of 2nd ranked column.

Column Name	Column Values	New Column
% E	500	11% E
% E	50	11% E
AA	200	2AA
AA	20	2AA
B	400	4B
B	40	4B
C	300	3C
C	30	3C
V	100	1V
V	10	1V

Appreciate any help here! Thanks

[jasperlch · Accepted solution]

Hi @BK209 

I would suggestion creating a new field called ColumnID then using this field as Column Headers in the Cross Tab tool. Finally use Dynamic Rename to get the column names back.

[BK209]

HI

Thank you so much that helped me lot. Appreciate your help.

[Shohn]

Here is a macro that mimics the crosstab tool with added asc/desc header columns.