Transform my data into a specific report structure
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
Solved! Go to Solution.
- Labels:
- Parse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ShelleyWu
This maybe can help you as a start.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
![](/skins/images/1A7F54316481E10DBCA4A87A32E06CC6/responsive_peak/images/icon_anonymous_message.png)