Sorting the columns based on the values

[Arvindcodes]

Dear Users, 

i am trying to use Cross tab tool in the below table 

ID	first name	Last name	Type	expense type	Amount
1	FN1	LN1	Office	car	100
1	FN1	LN1	Office	bike	50
1	FN1	LN1	Office	van	500
2	FN2	LN2	Home	car	1000
2	FN2	LN2	Home	bike	100
1	FN1	LN1	Office	car	200

in order to get the output similar to 

ID	first name	Last name	Type	sum_car	sum_van	sum_bike	total
2	FN2	LN2	Home	1000	0	100	1100
1	FN1	LN1	Office	300	500	50	850
			total	1300	500	150	1950

But when i use the cross tab tool, it gives me new columns in the ascending order of the alphabets i.e a-z so i get Sum_bike, Sum_Car and sum_Van. 

Could you please help me get the column sorted in the order its total value ? 

Regards,

ASR

[ShankerV]

Hi @Arvindcodes 

It is possible to achieve via multiple ways. 

One way is to use Select tool to rearrange the columns,

Many thanks

Shanker V

[ShankerV]

.

[PanPP]

Hi @Arvindcodes 

I have uploaded a sample WF to achieve your output.

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution so other community members can see it. If you have any other questions, please let us know.

[SmitaLohande]

Hi @Arvindcodes 

You can achieve desired output using the Summary and Cross Tab tool.

To get the Row Totals, I have further summarized and then Union'ed the results.

I have attached my solution to the response, tried to make it as simpler as I could :)

Please do have a look and let me know any queries.

Also please mark it as solution if it turns out to be useful and has helped to achieve your results :)

Good Luck !!

Many Thanks :)