×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

transform data from many rows to one column

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

RE: transform data from many rows to one column

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.

RE: transform data from many rows to one column

(OP)
thanks francisl
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

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

RE: transform data from many rows to one column

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"

RE: transform data from many rows to one column

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.

RE: transform data from many rows to one column

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

RE: transform data from many rows to one column

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.



RE: transform data from many rows to one column

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.

RE: transform data from many rows to one column

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))



RE: transform data from many rows to one column

(OP)
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

RE: transform data from many rows to one column

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.

CODE

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: www.essexsteam.co.uk

RE: transform data from many rows to one column

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

RE: transform data from many rows to one column

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.


RE: transform data from many rows to one column

(OP)
thanks indeed ivymike
george

RE: transform data from many rows to one column

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!

RE: transform data from many rows to one column

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?

RE: transform data from many rows to one column

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

RE: transform data from many rows to one column

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!

RE: transform data from many rows to one column

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

RE: transform data from many rows to one column

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!

RE: transform data from many rows to one column

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.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close