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

 

[FrancisL]

If you only have to do this once, you do quickly it manually.
Select each row block, copy it, then use Edit Paste Special Transform to paste it as a column.

 

[stamopoulos]

thanks francisl
i am afraid that the rows are many hundrends
i cannot do that manually, i need some other way

 

[FrancisL]

Sorry, I am not an Excel VBA expert but I would imagine you can write a module that goes something like
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

 

[melone]

Here is a snipet of code that I use in one of my spreadsheets. Hope this helps!

'''''''''' 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"

 

[quark]

If you want to avoid selecting cells twice(as suggested by FrancisL, first columns and then rows), here is an easier way. You have to select only once.

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.

 

[quark]

oops, I think we were all typing at the same time. Do I need to say that I should have refrained from posting it?

 

[ivymike]

if you want to use formulas, then try the following:

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.

 

[FalsePrecision]

ivymike, your formula doesn't quite work...every value in column CV is not indexed properly (the values in column A are indexed instead).

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.

 

[ivymike]

oops, replace my formula for cell B55 above with the following:
=INDEX($A$3:$CV$53,INT((A55-1)/100)+1,A55-100*INT((A55-1)/100))

 

[ivymike]

example sheet:

http://mywebpages.comcast.net/iefox2/rowtocol.html

 

[stamopoulos]

Thank you everybody

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

 

[johnwm]

Just copy and paste this into a macro. You will need to adjust intMaxRow and intMaxCol to suit your spreadsheet, and change the name of the sheets to suit.

Dim intMaxRow As Integer, intCurrentRow As Integer
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:

http://www.essexsteam.co.uk

 

[stamopoulos]

thanks johnwm , i tried to do that but looks impossible for me, not vb environment oriented at all

 

[ivymike]

take a look at the sample sheet, for one thing, and read the help text for the index function. The function returns REF if you have asked for a column from outside the range of the matrix it's looking through.

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.

 

[stamopoulos]

thanks indeed ivymike
george

 

[supersway]

I was able to cut and paste 250 X 6000 cells in to a Word document, and then replace all the tab delimiters with paragraph marks. And wah-laa-->one column. Fun!

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!

 

[BzBeauty]

hi,
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?

 

[LCruiser]

Same way. Copy, go to Word, Paste Special unformatted text, Replace paragraph marks with a comma.

 

[supersway]

Yep! I agree. I used the Microsoft Word "Replace" function under the Edit menu.

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!