jakeman
Active Member
- Joined
- Apr 29, 2008
- Messages
- 325
- Office Version
- 365
- Platform
- Windows
Hi there - I'm working with a data set that is in a less than friendly format. I'm trying to turn this data set into a more user friendly format for querying and pivot tables.
Here's how the data is currently structured:
<tbody>
</tbody>
Instead of the above format, I'd like to write a macro that will step through each column where there is a control listed and if there is more than one control, it will append the Business Entity and the Control number to a new row. So I'd like my new list to look this way:
<tbody>
</tbody>
Can anyone share a method for this approach?
Here's how the data is currently structured:
Business Entity | Control1 | Control2 | Control3 | Control4 | Control5 | Control6 | Control7 | Control8 | Control9 | Control10 |
Legal | Cntl_001 | Cntl_002 | ||||||||
Marketing | Cntl_001 | Cntl_002 | Cntl_005 | |||||||
Operations | Cntl_017 | Cntl_018 | ||||||||
Investments | Cntl_006 | Cntl_007 |
<tbody>
</tbody>
Instead of the above format, I'd like to write a macro that will step through each column where there is a control listed and if there is more than one control, it will append the Business Entity and the Control number to a new row. So I'd like my new list to look this way:
Business Entity | Control # |
Legal | Cntl_001 |
Legal | Cntl_002 |
Marketing | Cntl_001 |
Marketing | Cntl_002 |
Marketing | Cntl_005 |
Operations | Cntl_017 |
Operations | Cntl_018 |
Investments | Cntl_006 |
Investments | Cntl_007 |
<tbody>
</tbody>
Can anyone share a method for this approach?