Traspose multiple rows data in one coloumn

[yasirbintariq]

I have sales figures of 100 companies from 2003 to 2010 i.e.
Company 1 03Sales 04Sales 05Sales --- 08Sales
Company 2 03Sales 04Sales 05Sales --- 08Sales
'
'
'
Company 100 03Sales 04Sales 05Sales --- 08Sales

I wanted to transpose this data so that it all sales figures comes in one column i.e.
03Sales
04Sales
,
,
,
08Sales
03Sales
04Sales.

Is there a direct way of doing it by one command, or I have to manually cut paste the transposed values.

 

[sous2817]

Do you just want each company's data transposed (meaning one company per column)? If so, select the range of sales for the company, select copy, then select the cell where you want the values to go, right click and select Paste Special. At the very bottom right of the Paste Special dialog box, there is an option to "Transpose". Tick that box and hit "OK".

 

[yasirbintariq]

No, I want all the data in 100 rows ( 1 X 8) to be transposed in the one column.

e.g.
a1 a2 a3
b1 b2 b3
c1 c2 c3

so I want the above data in one column like this.

a1
a2
a3
b1
b2
b3
c1
c2
c3

I am looking for a formula or command which can do it in one instance.

 

[sous2817]

You're not going to find a command or formula to do it in one instance. You're going to need some VBA to accomplish this.

 

[yasirbintariq]

Can any body help with that. I found a vba code for this type of action but I am unable to customize it. My data range is A1:I1 to A123: I123

 

[sous2817]

This should get you close. Just update the output range to be where ever you want (it's currently set to Column P of Sheet 1).

Sub Macro1()

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Dim LR1 As Long, LR2 As Long
Dim i As Long

With Sheet1

    LR1 = .Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LR1
    
        Range(.Cells(i, 1), .Cells(i, 9)).Copy
        LR2 = .Range("P" & Rows.Count).End(xlUp).Row
        .Cells(LR2, 16).PasteSpecial Transpose:=True
    Next i
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[yasirbintariq]

My actual range is A1:H1 to A123:H123
I tried to use the above suggested macro after replacing 9 with 8 at line

Range(.Cells(i, 1), .Cells(i, 9)).Copy

but when I run it , it transpose the values in first 7 column, thus leaving the
value in column H unattended.
How to correct it.
</pre>

 

[trunten]

I think if I've understood correctly you could do it with formulas and a couple of helper cells.
Cell K1 = 1
Cell L1 = 2
Cell J1 = =INDEX(A:H,K1,L1)
Cell J2 = =INDEX(A:H,K2,L2)
Cell K2 = =IF(MOD(ROW(A1),7)=0,1,0)+K1
Cell L2 = =MOD(ROW(A1),7)+2

Then fill J2:L2 down to row 700

 

[yasirbintariq]

this formula worked partially. It misses values in column A i.e. values a1, a2, a3 are not in the transposed list. However it perfectly transpose the values in the remaining range. i.e. B1:H1 to B123 to H123.

Please tell me how to fix it.

 

[bosco_yip]

No, I want all the data in 100 rows ( 1 X 8) to be transposed in the one column.

e.g.
a1 a2 a3
b1 b2 b3
c1 c2 c3

so I want the above data in one column like this.

a1
a2
a3
b1
b2
b3
c1
c2
c3

I am looking for a formula or command which can do it in one instance.

1] Your datas at range A1:C3

2] D1, enter formula and copy down :

=IF(ROW(1:1)<=COUNTA(A$1:C$3),INDEX(A$1:C$3,INT(ROW(3:3)/3),MOD(ROW(3:3),3)+1),"")

Regards