colum to rows (officexp 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I think the macro below does what you want.

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

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:

                .Range("A1").Offset(I + 1, 0).EntireRow.Delete

Reply | Quote

I think the macro below does what you want.

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

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:

                .Range("A1").Offset(I + 1, 0).EntireRow.Delete

Reply | Quote