How to setup macro for data set

[jakeman]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Business Entity[/TD]
[TD]Control1[/TD]
[TD]Control2[/TD]
[TD]Control3[/TD]
[TD]Control4[/TD]
[TD]Control5[/TD]
[TD]Control6[/TD]
[TD]Control7[/TD]
[TD]Control8[/TD]
[TD]Control9[/TD]
[TD]Control10[/TD]
[/TR]
[TR]
[TD]Legal[/TD]
[TD]Cntl_001[/TD]
[TD]Cntl_002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD]Cntl_001[/TD]
[TD]Cntl_002[/TD]
[TD]Cntl_005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Cntl_017[/TD]
[TD]Cntl_018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investments[/TD]
[TD]Cntl_006[/TD]
[TD]Cntl_007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Business Entity[/TD]
[TD]Control #[/TD]
[/TR]
[TR]
[TD]Legal[/TD]
[TD]Cntl_001[/TD]
[/TR]
[TR]
[TD]Legal[/TD]
[TD]Cntl_002[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD]Cntl_001[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD]Cntl_002[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD]Cntl_005[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Cntl_017[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Cntl_018[/TD]
[/TR]
[TR]
[TD]Investments[/TD]
[TD]Cntl_006[/TD]
[/TR]
[TR]
[TD]Investments[/TD]
[TD]Cntl_007[/TD]
[/TR]
</tbody>[/TABLE]

















Can anyone share a method for this approach?

 

[rpaulson]

try this

Sub Do_it2()

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
For c = 2 To Cells(r, Columns.Count).End(xlToLeft).Column

With Worksheets("Sheet2")

lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(lr, "A") = Cells(r, "A")
.Cells(lr, "B") = Cells(r, 2)
End With
Next c
Next r


End Sub

 

[jakeman]

Hey there - thanks for your response. I can't quite get this to work right.

Let's suppose that my data resides on a sheet called "Current_List" and the range of values begin on row 4 of column I to row 265 of column S. It would be ideal if I could empty the results of this macro to a sheet called Sheet2, where the data would begin on row 2 of that sheet. How would you revise your code to accommodate that change?

Thanks, man.

 

[rpaulson]

Sub Do_it()

For r = 4 To Cells(Rows.Count, "I").End(xlUp).Row
For c = 10 To Cells(r, Columns.Count).End(xlToLeft).Column

With Worksheets("Sheet2")

lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(lr, "A") = Cells(r, "I")
.Cells(lr, "B") = Cells(r, c)
End With
Next c
Next r


End Sub