coffeenoir
New Member
- Joined
- Jul 11, 2017
- Messages
- 1
I’m working with a dataset that has 6 columns and approximately 5000 rows. Column A contains the name of a training course. Columns B through F have event types. Each training course can have multiple event types. So, for example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD="align: center"]Event Type 1[/TD]
[TD="align: center"]Event Type 2[/TD]
[TD="align: center"]Event Type 3[/TD]
[TD="align: center"]Event Type 4[/TD]
[TD="align: center"]Event Type 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T4[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T7[/TD]
[TD]Workshop[/TD]
[TD]Drill[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T9[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T6[/TD]
[TD]Exercise[/TD]
[TD]Drill[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like to create a macro that flattens this dataset out. I’m looking for a macro that accomplishes the following:
So, after running the macro, I would like it to rearrange my example above into the following configuration:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD] B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD="align: center"]Event Type 1[/TD]
[TD="align: center"]Event Type 2[/TD]
[TD="align: center"]Event Type 3[/TD]
[TD="align: center"]Event Type 4[/TD]
[TD="align: center"]Event Type 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T4[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T7[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T7[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T7[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]T9[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]T6[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]T6[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]T6[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any help is appreciated!
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD="align: center"]Event Type 1[/TD]
[TD="align: center"]Event Type 2[/TD]
[TD="align: center"]Event Type 3[/TD]
[TD="align: center"]Event Type 4[/TD]
[TD="align: center"]Event Type 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T4[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T7[/TD]
[TD]Workshop[/TD]
[TD]Drill[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T9[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T6[/TD]
[TD]Exercise[/TD]
[TD]Drill[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like to create a macro that flattens this dataset out. I’m looking for a macro that accomplishes the following:
- Looks at the Event Type 2 column (Column C)
- If cell C2 has content, add a new row below that item
- Copy over the content from A2 into the new row
- Copy the content in C2 (Event 2) into C3 (Event 1) in the new row
- Do the same for the rest of the event types
- Do the same for the rest of the line items
So, after running the macro, I would like it to rearrange my example above into the following configuration:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD] B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD="align: center"]Event Type 1[/TD]
[TD="align: center"]Event Type 2[/TD]
[TD="align: center"]Event Type 3[/TD]
[TD="align: center"]Event Type 4[/TD]
[TD="align: center"]Event Type 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T4[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T7[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T7[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T7[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]T9[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]T6[/TD]
[TD]Exercise[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]T6[/TD]
[TD]Drill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]T6[/TD]
[TD]Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any help is appreciated!