Convert monthly and annual data into columns

[jbcrut01]

I have data arranged as follows:

Jan Feb Mar Apr May etc
2011 999 99 99 99 99
2010 99 99 99 99 99
2009
etc

I want to convert this into a two columns: (1) column registering the month and year and (2) a column with the data. How do I make this happen?

 

[venkat1926]

for checking purposes I h ave the changed the data. sample data in sheet 1 is like this

Excel Workbook
	A	B	C	D	E	F
1		Jan	Feb	Mar	Apr	May
2	2011	1	2	3	4	5
3	2010	6	7	8	9	10
Sheet1

now run this macro test and see sheet2

Sub test()
Dim rrow As Range, rcol As Range, crow As Range, ccol As Range, result As Range
Worksheets("sheet1").Activate
Set rrow = Range(Range("B1"), Range("B1").End(xlToRight))
Set rcol = Range(Range("A2"), Range("A2").End(xlDown))
For Each crow In rrow
For Each ccol In rcol
With Worksheets("sheet2")
Set result = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
result = crow & " " & ccol
result.Offset(0, ccol.Column) = Intersect(Columns(crow.Column), Rows(ccol.Row))
 Next ccol
Next crow
End Sub

Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub