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:

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?

 

[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