Rearranging data in form of Panel Data

[leonelcd]

Hello guys,


Is there any way to rearrange data in this way for the following:


: BEFORE:

/////////[Ticker A] [Ticker B] [Ticker C]
2010 ///// 0,3 ////// 0,6 /////// 0,9
2011 ///// 0,7 ////// 1,4 /////// 2,1
2012 ///// 1,3 ////// 2,6 /////// 3,9

: LATER :

2010 Ticker A 0,3
2011 Ticker A 0,6
2012 Ticker A 0,9
2010 Ticker B 0,7
2011 Ticker B 1,4
2012 Ticker B 2,1
2010 Ticker C 1,3
2011 Ticker C 2,6
2012 Ticker C 3,9


Worth function, worth macro, what worth everything that's automate this process. Are spreadsheets with hundreds of rows.


Thanks!

 

[hippiehacker]

not sure what you are looking for as the end result as your later table makes no sense compared years to ticker to value but how about the following?

Sub ConvertTableToList()
Const TEST_COLUMN As String = "A"
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = iLastCol To 2 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 3).Value = .Cells(i, j).Value
.Cells(i + 1, 2).Value = .Cells(1, j).Value
.Cells(i + 1, 1).Value = .Cells(i, 1).Value
.Cells(i, j).Value = ""
Next j
.Rows(i).Delete xlUp
Next i
.Rows(1).Delete
End With
Application.ScreenUpdating = True
End Sub

 

[leonelcd]

Hi hippiehacker, yes!

The only thing I want to change is the result: Could be in the different columns? Like this:

2010 //// Ticker A //// 0,3
2011 //// Ticker A //// 0,6
(...)

 

[leonelcd]

Hi hippiehacker, forget what I say! That's exactly what I want. Is there anyway to vote/make a "like" to you? How can I close the thread?