transform data from many rows to one column 1

[stamopoulos]

dear group

my data is as following in ms excell:

x1 x2 x3 x4 x5
x6 x7 x8 x9 x10
x11 x12 x13 x14 x15
.
.
.
.
and i want to transform it as following:
x1
x2
x3
x4
x5
x6
x7
x8
.
.
.

i would be gratefull if you could give help me

 

[cummings54]

Here's yet another VBA routine.
In this one you select the cells containing the data set you want to transform and then run the macro. Make sure the data set is not located on Sheet2 or the macro will overwrite data

Sub PutSelectionInOneColumn()
Dim lng As Long
lng = 1
For Each cl In Selection
Sheet2.Cells(lng, 1).Formula = cl.Formula
lng = lng + 1
Next cl
End Sub

 

[supersway]

Fun!! That is awesome. This is the first VBA I have ever used thanks for the demo. I awkwardly added the ability to scroll to the next column if the records exceed 60,000 rows.

Even on my slowest computer this ran 1.2 million cells in about 10 minutes without locking up.

Sub PutSelectionInOneColumn()
Dim wdt As Wide
wdt = 1
Dim lng As Long
lng = 1
For Each cl In Selection
Sheet2.Cells(lng, wdt).Formula = cl.Formula
lng = lng + 1
If lng > 60000 Then wdt = wdt + 1
If lng > 60000 Then lng = 1
Next cl
End Sub

Thanks again!

 

[bacon4life]

I had set of data that was only 6 narrow columns wide, but about 600 lines long, so I wanted to do the opposite. To get it to wrap like newspaper columns, I filled 30 columns and 120 lines with :

=INDIRECT(CONCATENATE("'final sorted'!R[",FLOOR(Irows/pcols,1)*(FLOOR((COLUMN()-1)/icols,1)),"]C[-",FLOOR((COLUMN()-1)/icols,1)*icols,"]"),FALSE)

Where the data was in "final sorted" and the data was Irows tall by icols wide and I wanted to print it pcols*icols wide.

The advantages of this are that the data in the original sheet can still be resorted, and to change the number of printing rows, just change pcols and drag the printable area boundaries to the right spot.

Now to get the data back to one set of columns:
=INDIRECT(CONCATENATE("'newspaper'!R[-",FLOOR((ROW()-2)/(prows),1)*(prows),"]C[",FLOOR((ROW()-2)/(prows),1)*icols,"]"),FALSE)

Where prows is how many rows your data has in newspaper format.

See and example of this at

http://www.mooload.com/file.php?file=files/1132031608/rune+words-for+eng+tips.xls

There is no need to go to word to transpose just one row or column. Just use copy then Edit-Paste Special. Then select transpose.

Excel can also find and replace. You can also copy & paste into the Find & Replace dialog boxes since excel does not have the special characters box. Note that you have to use ctrl-v to paste. Another trick is that alt-enter puts in a paragraph mark in normal editing, but you have to use ctrl-enter in the find and replace dialog box.