Transform my data into a specific report structure

Question

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:

data.xlsx

Qiu · Accepted Answer

@ShelleyWu 
This maybe can help you as a start.

Yoshiro_Fujimori · Accepted Answer

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.