Transform my data into a specific report structure

[ShelleyWu]

Hi everyone,

I have a list of data (attached) with the first column containing column names "CCY, Account Name, Account Number, as well as LCY Balance and USD Balance for each month (Jan. to Dec.)". I would like to transform my data into the expected report structure below, with distinct values in the original column A (excluding the first row). Can you please help me with how I can generate my expected format? Thank you very much.

Raw data

Expected report structure: 

[Qiu · Accepted solution]

@ShelleyWu 
This maybe can help you as a start.

[Yoshiro_Fujimori · Accepted solution]

Hi @ShelleyWu ,

Here is one way of doing this.

Steps

1. Add RecordID fo each row in the output format.

2. Cross Tab with

Group-by "Record ID" 

Change Column Header "ColumnName"

Values for New Columns "Value"

3. Select tool to arrange the order of Columns

(You may not need this step if you use numbers (e.g. "01" instead of "January") in the column names for Balance Rows)

Wofkflow

Output

Good luck.