transform data from many rows to one column
transform data from many rows to one column
(OP)
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
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





RE: transform data from many rows to one column
Select each row block, copy it, then use Edit Paste Special Transform to paste it as a column.
RE: transform data from many rows to one column
i am afraid that the rows are many hundrends
i cannot do that manually, i need some other way
RE: transform data from many rows to one column
For i = 1 to NumRows 'number of rows
For j = 1 to NumCols 'number of columns
Copy cell("R" & i & "C" & j)
Paste to another sheet to cell("R" & (i-1)* NumCols + j & "C1"))
next
next
RE: transform data from many rows to one column
'''''''''' Put in 1 column ''''''''''''''''''''''''''''''
Application.StatusBar = "Unstack connections"
i = 4
j = 5
Do While (Sheet1.Cells(i, 1) <> "")
Do While (Sheet1.Cells(i, 1) > 1)
Rows(i + 1).Select
Selection.Insert Shift:=xlDown
Sheet1.Cells(i + 1, 1).Value = 1
Sheet1.Cells(i + 1, 2).Value = Sheet1.Cells(i, 2).Value
Sheet1.Cells(i, 1).Value = Sheet1.Cells(i, 1).Value - 1
Loop
j = 1
Do While (Sheet1.Cells(i, 4) <> "")
Sheet1.Cells(i, 4).Select
Selection.Copy
Sheet1.Cells(i + j, 3).Select
ActiveSheet.Paste
Sheet1.Cells(i, 4).Select
Selection.Delete Shift:=xlToLeft
j = j + 1
Loop
i = i + j
Loop
Application.StatusBar = "Unstack connections - Done"
RE: transform data from many rows to one column
Go to the first row where you want to start transposing(say a2) and select no.of rows equal to that of columns(say 10). Use the formula =transpose(a1:j10) and then press ctrl+shift+enter. Same procedure for next row and so on.
RE: transform data from many rows to one column
RE: transform data from many rows to one column
assuming you have 100 columns and 50 rows of data
columns are A thru CV
rows are 3 thru 53
fill cells a55 thru a5153 with ascending numbers 1 thru 5099
in cell b55, enter the following formula
=INDEX($A$3:$CV$53,INT(A55/100+1),INT(A55-INT(A55/100)*100))
copy it down the column thru cell b5153
the values returned by the formula should be what you wanted.
RE: transform data from many rows to one column
Try this:
cell B55=INDEX($B$3:$CV$53,D55,E55)
(copy down the sheet)
cell D55=IF(E55=100,TRUNC(A55/100),TRUNC(A55/100)+1)
(copy down the sheet)
cell E55:
=IF(100*(A55/100-TRUNC(A55/100))=0,100,100*(A55/100-TRUNC(A55/100)))
(copy down the sheet)
Now, ignore the jeers of the VBA-enabled, and see if that works.
RE: transform data from many rows to one column
=INDEX($A$3:$CV$53,INT((A55-1)/100)+1,A55-100*INT((A55-1)/100))
RE: transform data from many rows to one column
http://mywebpages.comcast.net/iefox2/rowtocol.html
RE: transform data from many rows to one column
Ivymike, i almost jump crazy from relief during copying down your formula but unfortunately it transposed data only from the first row. after that i got the message REF!
Here is again the problem
LINE 1 X1 X2 X3 X4 X5
LINE 2 X6 X7 X8 X9 X10
.
.
.
LINE 1K . . . . X5K
I would be gratefull if you could review the formula and give me exact instructions (for instance is it necessary to create the last column you did (ie CV) in case i miss something, but obvious for all of you programming oriented)
I do appreciate for your help all of you
George
RE: transform data from many rows to one column
CODE
Dim intMaxCol As Integer, intCurrentCol As Integer
Dim intNewRow As Integer
intMaxRow = 5
intMaxCol = 5
For intCurrentRow = 1 To intMaxRow
For intCurrentCol = 1 To intMaxCol
intNewRow = ((intCurrentRow - 1) * intMaxRow) + (intCurrentCol - 1)
Sheets("originalsheet").Cells(intCurrentRow, intCurrentCol).Copy
Sheets("rearranged").Cells(intNewRow + 1, 1).Select
ActiveSheet.Paste
Next intCurrentCol
Next intCurrentRow
I know this can be optimised, but it's deliberately kept simple so everyone can see how it works.
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: transform data from many rows to one column
RE: transform data from many rows to one column
I had 100 columns of data and 50 rows. The formula needs to be adapted to the number of columns and rows that you have.
Here's how it breaks down:
=INDEX($A$3:$CV$53,INT((A55-1)/100)+1,A55-100*INT((A55-1)/100))
Index(RANGE,rownum,colnum)
The index function looks up the value in range at the location (rownum, colnum) and returns it. It returns REF if (rownum,colnum) is outside of RANGE.
INT((A55-1)/100)+1
This formula converts the input number (the value in the incrementing column I mentioned above) and converts it to a rownum for your range. Modify this function to suit your needs as follows:
INT(((ref to incrementing number)-1)/(number of values per row))+1
In my case, since I had 50 rows and 100 columns, I set it up so that the following inputs gave the following outputs:
1 -> 1 (values 1 thru 100 are in the first row)
100 -> 1
101 -> 2 (values 101 thru 200 are in the second row
200 -> 2
201 -> 3
etc
A55-100*INT((A55-1)/100)
This formula converts the input number into a column number. Modify it also as needed:
(ref to incrementing number)-(number of columns)*int(((ref to incrementing number)-1)/(number of columns))
In my case, since I had 50 rows and 100 columns, I set it up so that the following inputs gave the following outputs:
1 -> 1 (values 1 thru 100 are in 100 cols in the first row)
50 -> 50
100 -> 100
101 -> 1 (101 is the first value in the second row)
150 -> 50
200 -> 100 (200 is the 100th value in the second row)
201 -> 1 (201 is the first value in the third row)
etc
I'll put up a modified version of my spreadsheet that should work with any number of rows&cols less than 50x100, which you can use for an example. Same URL as before.
RE: transform data from many rows to one column
george
RE: transform data from many rows to one column
Somewhat related, I read the next release of Excel will have 16,000+ columns. Exciting!
Also, thank you for the neat formulas, I worked through them and enjoyed the understanding.
Bye!
RE: transform data from many rows to one column
I want to do the opposite. go from one column to one row, with the information comma-delimited. I want to have it replace the paragraph marks with the comma space. How can I do this?
RE: transform data from many rows to one column
RE: transform data from many rows to one column
On the Word version I have, once the replace menu comes up, I have to then hit "more" and then "special" to view all the different marks that can be added and replaced.
Going from Paragraph marks to commas is very straight forward. Experiment on small amounts of data first. Also, you may later paste "text only" to eliminate any superfluous coding. Woo-Hoo!
RE: transform data from many rows to one column
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
RE: transform data from many rows to one column
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!
RE: transform data from many rows to one column
=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 h
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.