Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transposing Data (Excel 2000)

    I have an excel spreadsheet that looks like this,
    Empno-----4Q2002-----1Q2003---2Q2003---3Q2003
    34567------40,000------2300-------40,000----16000
    67352------30,000------2400-------30,000-----27872

    What I need is to transpose the data to look like this:
    Empno ---Quarter----Amt
    34567-----4Q2002---40,000
    34567-----1Q2003---2,300
    34567-----2Q2003---40,000
    34567-----3Q2003---16,000
    67352-----4Q2002---30,000
    67352-----1Q2003----2,400
    67352-----2Q2003----30,000
    67352-----3Q2003----27,872

    Any information would be great! Thanks in advance. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing Data (Excel 2000)

    Let's assume your data is in columns A:E

    If you just need to do this once, I would insert a column between Empno and 4Q2002. Type 2002Q4 (instead of 4Q2002) into all the cells in this blank column. Then cut and paste column D to the bottom of column C. Then type 2003Q1 in column B corresponding to these new rows. Repeat for each calendar quarter of data. Then copy and paste the Empno's three times down column A. Then sort your new list by empno and quarter.

    If this is something you will be doing repeatedly, I (or someone else) can construct a macro for you.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Transposing Data (Excel 2000)

    Try this macro. Select the range to convert (include the "header") and then run the macro.

    Steve

    <pre>Sub ConvertMe()
    Dim rng As Range
    Dim wksNew As Worksheet
    Dim rCell As Range
    Dim iCol As Integer
    Dim lRow As Long
    Dim lNewRow As Long

    Set rng = Selection

    lNewRow = 1
    Set wksNew = Worksheets.Add

    With wksNew
    .Cells(lNewRow, 1) = "Empno"
    .Cells(lNewRow, 2) = "Quarter"
    .Cells(lNewRow, 3) = "Amt"
    lNewRow = lNewRow + 1

    For lRow = 2 To rng.Rows.Count
    For iCol = 2 To rng.Columns.Count
    .Cells(lNewRow, 1) = rng.Cells(lRow, 1)
    .Cells(lNewRow, 2) = rng.Cells(1, iCol)
    .Cells(lNewRow, 3) = rng.Cells(lRow, iCol)
    lNewRow = lNewRow + 1
    Next iCol
    Next lRow
    End With
    End Sub</pre>


  4. #4
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing Data (Excel 2000)

    Your the greatest!! <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

    Thanks!! <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing Data (Excel 2000)

    This code may not be the most efficient but it does the job:

    First, below your db records enter the 3 field names, something like:
    A10 = Empl
    B10 = Qtr
    C10= Amt
    Adjust the row number as needed.

    Then enter this code in a standard module and run the code. It will copy the data to the new table.
    If you have a very large db then you may need to adjust this to place it on another worksheet.

    Sub TransposeData()

    Dim oCell As Range
    For Each oCell In Range("A2", "A10") 'adjust to fit you db
    Range("B1:E1").Copy
    Range("B50000").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
    Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(3, -1)) = oCell
    Range(oCell.Offset(0, 1), oCell.Offset(0, 4)).Copy
    ActiveCell.Offset(0, 1).PasteSpecial Transpose:=True

    Next

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •