## 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.