Lining up disjointed columns within same dataset?
- 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
I have a report that I'd like to transform so that it can be batch imported to our software.
The report looks something like this:
Name: Charlie | |||
Exercise | Light | Medium | Hard |
Run | 15 | 10 | 2 |
Jump | 20 | 10 | 2 |
Swim | 20 | 5 | 5 |
Name: Becky | |||
Exercise | Light | Hard | |
Run | 30 | 12 | |
Jump | 30 | 12 | |
Swim | 25 | 12 |
I need this:
Name | Exercise | Light | Medium | Hard |
Charlie | Run | 15 | 10 | 2 |
Charlie | Jump | 20 | 10 | 2 |
Charlie | Swim | 20 | 5 | 5 |
Becky | Run | 30 | 12 | |
Becky | Jump | 30 | 12 | |
Becky | Swim | 25 | 12 |
Using the multi-row formula I've been able to create the name column:
IF Contains([Field1]],"Name:") THEN trim(Replace([Field1],"Name:",""))
ELSE [Row-1:Name] ENDIF
This adds a column and layers in the name until a new name appears. But I can't seem to figure out how to work with the gap in columns. When one person doesn't have data in a specific column it is omitted, but I need them all to lineup as shown.
Suggestions?
Solved! Go to Solution.
- Labels:
- Preparation
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @CGIQV ,
I've attached the workflow for you.
This was solved using a good old multi-row formula to reference the row above to add the name to every row.
I hope this helps.
M.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This got the first half, but I think is missing the second half.
Names are lining up appropriately which I had a different way of getting done. My issue is the disjointed columns. Becky only had data under light and hard, not medium. The resulting workflow places her hard data under medium:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I've been tinkering with this for a while to try and apply it to my data set. I'm not understanding how the macro is being integrated.
The data set was an example to provide a conceptual basis of the issue which was having data layered one on top of another where the columns did not align. But it may be 100 people and 10 different parameters where each person is only utilizing a few.
How would the macro need to be modified to account for this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It will work if the data is consistent, but if there are variations not represented in the example data then it won't handle that.
If you could provide a larger dataset more representative of the format you will be using then I can amend accordingly.
All the macro does is individually parses each record into the required format, then unions the results.
M.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
On my own I can get the file to look something like this:
Chuck | Exercise | Slow | Medium | Fast | Extra Fast |
Chuck | Run | 1 | 2 | 5 | 4 |
Chuck | Jump | 1 | 5 | 8 | 2 |
Chuck | Swim | 1 | 5 | 8 | 2 |
Chuck | Bike | 1 | 5 | 8 | 2 |
Mary | Exercise | Slow | Fast | Extra Fast | |
Mary | Run | 1 | 8 | 4 | |
Mary | Bike | 5 | 6 | 3 | |
Gary | Exercise | Fast | Extra Fast | ||
Gary | Run | 1 | 15 | ||
Gary | Bike | 4 | 5 | ||
Gary | Swim | 2 | 8 | ||
Ryan | Exercise | Medium | Extra Fast | ||
Ryan | Bike | 9 | 20 |
But ultimately I want I single header column (edit: I mean row), not each person to have their own header independent header which prevents the data from being consistent top to bottom.
Thanks in advance for the help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What I meant to say was I can ultimately transposed my dataset into the format I showed in the most recent post, so I've bypassed the need for the name to be layered in as a new column.
I still am unsure how the macro is parsing the data from the columns appropriately.