Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    colum to rows (officexp 2002)

    i have 2 colums of approx 15000 rows.colum "A " has a name in 1 cell and the next could be cell 2 or cell 22, colum "B" has figure in each cell.
    how can i take the colum of figures in colum "B" and move them so that they form a line

  2. #2
    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: colum to rows (officexp 2002)

    Add this formula to C1:
    =IF($A1<>"",INDEX($B1:$B4,COLUMN()-1),"")
    Copy this to D1:E1
    Copy C1:E1 to C2:Ewhatever (autofill)
    Select cols C-E and copy then paste special values
    I assume you want to get rid of "blank rows"
    data- autofilter
    select column A and choose "blanks"
    Select the rows and delete them
    Remove the autofilters

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colum to rows (officexp 2002)

    I think the macro below does what you want.

    <pre>Public Sub TransVals()
    Dim I As Long, J As Long
    I = 0
    J = 0
    With ActiveSheet
    Do While .Range("B1").Offset(I + 1) <> ""
    If .Range("A1").Offset(I + 1) <> "" Then
    I = I + 1
    J = 0
    Else
    .Range("C1").Offset(I, J).Value = .Range("B1").Offset(I + 1, 0).Value
    J = J + 1
    .Range("A1").Offset(I + 1, 0).EntireRow.Delete
    End If
    Loop
    End With
    End Sub
    </pre>


    This macro also deletes the rows after moving the value from column B. If you don't want to do that, then remove the like that reads:

    <pre> .Range("A1").Offset(I + 1, 0).EntireRow.Delete
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colum to rows (officexp 2002)

    Steve: It looks like your solution always expects there to be exactly three rows that need to be moved to columns. The post indicated that there could be between two and twenty two.
    Legare Coleman

  5. #5
    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: colum to rows (officexp 2002)

    After rereading it the post, I think that you are correct.

    And if you are, then I agree that a macro would be much better than a formulaic approach (i am not even sure a formula approach would even work)

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colum to rows (officexp 2002)

    sorry for not replying sooner but i have been out of town all weekend, just a quickiereply.

    yes the number of rows can vary from 2 to 22 wich i agree is very dishartening if you have 500 plus calculations to make.

    i will get back to you tommorow with a result

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colum to rows (officexp 2002)

    it works a treat very quick as well thank yo to Legare Coleman for his solution and to all who replied
    thank you one and all

Posting Permissions

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