How to transpose these data in Excel 2010?

[zicitron]

Hi all,

I am using Excel 2010. I have a set of data as shown in the image below:

I need to reformat the data so that I have ONLY 2 columns, namely "RN" and "PKG REV". Thus I need all the figures relating to "RN" to fall into the new "RN" column and same thing for "PKG REV".

Is there a way to transpose this data? The "transpose" option in the "Paste Special" dialog box does not seem to be the right way.

Thanks for your help!

 

[patel45]

attach a sample xlsx file with current data and desired result

 

[ZAX]

Here,Try This Code:

Sub ReformatData()
For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    Range(Cells(2, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Select
    Selection.Copy
    Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial
    Application.CutCopyMode = False
Next
For C = 4 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    Range(Cells(2, C), Cells(Cells(Rows.Count, C).End(xlUp).Row, C)).Select
    Selection.Copy
    Cells(Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).PasteSpecial
    Application.CutCopyMode = False
Next
Range(Cells(1, 3), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column)).Delete
End Sub

ZAX

 

[zicitron]

Fantastic Zax! Just what I needed! You rock!

Thanks a lot!