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 dataExpected OutputdataA11A1 1 A22A22A33A33AA11B11AA22B22AA33B33B11AA11B22AA22B33AA33 Furthermore, I would also like to know how can I convert the table structure into the following like excel ABAA1Data1Data1Data12Data2Data2Data23Data3Data3Data3 cell_num.xlsx

Sorting the column into alphabet order

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

cell_num.xlsx

Developer

Designer Cloud

Common Use Cases

Bug

Batch Macro

Join

Preparation

Interface Tools

Installation

Data Investigation

Machine Learning

Database Connection

Demographic Analysis

Chained App

AMP Engine

Prescriptive Analytics

AMP-Engine

Accepted answers

DataNath

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

Sort and cross-tab Excel headers.yxzp

All comments

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

Sort and cross-tab Excel headers.yxmd

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.