Sorting the column into alphabet order

[tamluenwai29]

Hi All, 

I got a column with different Excel cell number, and I would like to know how to order the column in alphabet order? 

Thanks all of you, and I have attached a sample data in this post down below

Original	data	Expected Output	data
A1	1	A1	1
A2	2	A2	2
A3	3	A3	3
AA1	1	B1	1
AA2	2	B2	2
AA3	3	B3	3
B1	1	AA1	1
B2	2	AA2	2
B3	3	AA3	3

Furthermore, I would also like to know how can I convert the table structure into the following like excel

	A	B	AA
1	Data1	Data1	Data1
2	Data2	Data2	Data2
3	Data3	Data3	Data3

[OTrieger]

@tamluenwai29 

What you can do is flag the data using REGEX in a new field
Something like:
IF REGEX_Match([Original], "\u\d") THEN 1
ELSEIF REGEX_Match([Original], "\u\u\d") THEN 2
ELSEIF REGEX_Match([Original], "\u\u\u\d") THEN 3
ect...

If you are expecting to have more than 1 digit then you can add a wildcard after the \d

Now you have mapping that you can use for sorting out the data

With a Sort Tool you can select your Mapping field as the first criteria and then Original as the second criteria. This will handle your first question.

Now with a Formula tool use REGEX_Replace([Original], "\d", "") now you got rid of the numbers

All left will be to Cross Tab the data, which will answer your second question

[DataNath]

Hey @tamluenwai29, how does this look? The general steps are:

- Use the Length() function to find the length of the headers, sort on this before their raw value (both ascending) to get them in the correct Excel order - fully dynamic

- Extract the actual name i.e. AA1 and AA2 both lose the number and become AA, as well as isolating 1 & 2 to get the index of the rows

- Create a column name index so that when we Cross-Tab, the fields are in the correct order and not just all of the A's first, then B's and so on

- Cross-Tab into the shape you're expecting

- Sort so that rows are ordered correctly

[tamluenwai29]

Hi, 

Thanks for your help, the workflow does help me a lot!!

However, I still have one problem regarding the cross-tab tools in the workflow.

Since the real data of column "data" is a string in my case. Therefore, it seems that I cannot use the same configuration that you made in cross-tab tools. 

The method for aggregating value in the cross-tab tools only shows "concatenate", "first", and "last". Whenever which method I select, the result is not what I expected. 

[DataNath]

Hi @tamluenwai29 - can you provide a sample of your actual data then? Or something that resembles it?

[tamluenwai29]

Hi, 

I have attached my dummy data in this comment down below, which is close to what I am working on now. 

Again, Thanks for your help!!

[DataNath · Accepted solution]

@tamluenwai29 is this closer to what you're looking for? I had assumed all fields would be consistent originally

[tamluenwai29]

Thanks for your help, it works!