ms Excel 2003, Data from columns to rows

Microsoft Excel 2003 (full)
September 21, 2010 at 10:57:48
Specs: Windows XP
Hi, I hope someone can help. I have the following data I would like to transpose without usin a macro or writing a script if at all possible. If not any help on the macro or script would be helpful:

A B C D E

1 period 1 period 2 period 3 period 4 Period n
2 abc 100 500 900 1300 etc
3 def 200 600 1000 1400 etc
4 ghi 300 700 1100 1500 etc
5 jkl 400 800 1200 1600 etc
6 n etc etc etc etc


I would like to take the above data and put it into the following format:

A B C
1 abc period 1 100
2 abc period 2 500
3 abc period 3 900
4 abc period 4 1300
5 def period 1 200
6 etc etc etc

Kind of going back to raw data from a pivot table formated data.

Thanks for any help.


See More: ms Excel 2003, Data from columns to rows

Report •


#1
September 22, 2010 at 09:53:51
A posting tip:

If you use the pre tags found above the text entry box at this site, you can line your data up to make it easier for us to read.

Click the Pre tag and enter your data between the tags and it will post like this:

       A           B          C          D          E            F

1               period 1   period 2   period 3   period 4     Period n
2    abc          100        500        900        1300         etc
3    def          200        600       1000        1400         etc

This code should get you what you want, assuming your input data starts in Sheet1!A1 and you want the new list placed in Sheet2!A1.

Keep in mind that macros cannot be undone, so I suggest that you test this in a backup copy of your workbook.

Option Explicit
Sub NewDataFormat()
Dim src_Col, src_Row, src_Row_Last As Integer
Dim dst_Row_Start, dst_Row, num_Cols As Integer
'Initialize Source Column, Destination Start Row and Last Source Row variables
  src_Col = 2
  dst_Row_Start = 1
  src_Row_Last = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Source Rows
   For src_Row = 2 To src_Row_Last
'Count the number of Columns with Data
     num_Cols = _
         WorksheetFunction.CountA(Sheets(1).Range(src_Row & ":" & src_Row)) - 1
'Loop though Destination Rows
'' Place Row Label from Sheet1!Column A into Sheet2!Column A as many times as
'' Required by the Columns count variable, Copy Period Number from Row 1 and
'' Copy Data from Current Source Row to each Destination Row
       For dst_Row = dst_Row_Start To dst_Row_Start + num_Cols - 1
         Sheets(2).Range("A" & dst_Row) = Sheets(1).Range("A" & src_Row)
         Sheets(2).Range("B" & dst_Row) = Sheets(1).Cells(1, src_Col)
         Sheets(2).Range("C" & dst_Row) = Sheets(1).Cells(src_Row, src_Col)
'Increment Source Column variable
          src_Col = src_Col + 1
       Next
'Determine new Destination Start Row for next Data Set
          dst_Row_Start = _
              Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Reset Source Column
          src_Col = 2
   Next
End Sub


Report •

#2
September 23, 2010 at 08:01:23
Thanks! No i did not know about "pre tag" to align up the data, when posting i aligned up the data then when i posted it pressed it all together. Thanks for still figuring out the data i know it was not very clear.

This macro works perfectly, this is a great community.

Thanks a ton!


Report •

Related Solutions


Ask Question