Hi ,

I have data in this format

Prod_code product jan feb mar
x1 xxx 23 32 34
y1 yyy 45 45 45
z1 zzz 89 89 89

and i need to convert the data in the below format

prod_code product month value
x1 xxx jan 23
y1 yyy jan 45
z1 zzz jan 89
x1 xxx feb 32
y1 yyy feb 45
z1 zzz feb 89
x1 xxx mar 34
y1 yyy mar 45
z1 zzz mar 89

Please help me in write a macro ......

Try this macro. The comments in the code should explain what is going on and allow you to modify it as suits.

Sub ReSortData()
Range("C1").Activate 'Assumes this is where Jan is
WriteToRow = 20 'This is how many rows down you want to start writing the data
Do While ActiveCell.Offset(1, 0) < "" 'Loop for every product row until a blank found
ProductRow = 1 'Holds the row to currently read
Do While ActiveCell.Offset(ProductRow, 0) < "" 'Loop for each Month across until a blank found
ActiveCell.Offset(WriteToRow, 0 - (ActiveCell.Column - 1)) = ActiveCell.Offset(ProductRow, 0 - (ActiveCell.Column - 1))
ActiveCell.Offset(WriteToRow, 0 - (ActiveCell.Column - 2)) = ActiveCell.Offset(ProductRow, 0 - (ActiveCell.Column - 2))
ActiveCell.Offset(WriteToRow, 0 - (ActiveCell.Column - 3)) = ActiveCell
ActiveCell.Offset(WriteToRow, 0 - (ActiveCell.Column - 4)) = ActiveCell.Offset(ProductRow, 0)
ProductRow = ProductRow + 1
WriteToRow = WriteToRow + 1
Loop
ActiveCell.Offset(0, 1).Activate
Loop
End Sub

Please excuse the lack of formatting - I'll work out how to do that one day. Just copy & paste the above as a new macro and adjust the variables as necessary and it should work. Give me a shout if any problems.