• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transposing multiple rows into one column

M

mbroswick

New Member
Hello,


I am wanting to transpose multiple rows into a single column. I am thinking that I need to use the OFFSET function, but I am not familiar enough with this formula to figure it out. Anyone have any ideas?


Thank you!
 
Hi mbroswick,


Welcome to the forums, Can you elaborate a little bit more regarding the problem?


Regards

Faseeh
 
Can you show an example of what you want? Don't multiple rows = a column of numbers?

To turn this:

[pre]
Code: 
1
2
3
4
into this:

1   2   3   4
[/pre]
Formula could be:

=INDEX(A:A,COLUMN(A1))

or vice versa

=INDEX(1:1,ROW(A1))
 
There's also the Copy, Paste Special - Transpose option, if this is a one time thing.
 
It looks like he wants to transpose one row into a column then at the bottom of the transpose, he wants to transpose another set of values
 
Sorry I will clarify.


I would like to turn this:


123

456

789


Into this:

1

2

3

4

5

6

7

8

9


I can take one row and transpose it into a single column, but I have 12 rows that I would like to put into a single column.


Thanks for the help!
 
Here's a formula that should work for any size range.

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)


Replace all the MyData with the absolute references to your Range (or, just define your range as "MyData" first).

Copy formula down as far as would be needed. If you want to hide error messages caused when you have too many formulas, can do:

=IF(ROWS(MyData)*COLUMNS(MyData)<ROW(A1),"",INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1))
 
Thanks Luke M!


One last question. In your first equation, you reference ROW(A1). I am setting up my new column in a different sheet than the 12 rows that I mentioned earlier. I am not sure which cell to reference in place of the A1 cell.


I hope that makes sense!


Thanks
 
@mbroswick

Don't replace the A1 with anything. It's actually just being used as a sort of "counter" to keep track of how many entries have been returned so far. The formula uses the COLUMNS function so many times because I was trying to make it work for any range. Small note: MyData has to be a rectangle. This wouldn't work:

[pre]
Code: 
1  3  4
2
5  6  7
[/pre]
In the sense that the formulas would display blanks for those 2 "empty" cells.


@Montrey

Hmm, not sure why it's not working...formula audit? Are you getting an error, or just the wrong output?
 
I had to name the range instead of just selecting the range. Wow awesome Luke. I'm amazed.

Gonna have to study this mother of a formula
 
@Montrey

Ah. If you select the range, you'll need to make them all absolute references and then it should work. Using a named range is a little easier since it automatically makes it absolute. Let me know if you need any help on the formula. =)
 
Thank you so much! I have 60 years of rainfall data that I need to manipulate and this just made it possible!


This does, however, make me realize that there is so much I need to learn about excel!


Thanks again!
 
Woot! Always glad to take a mundane manual task and turn it into a neat, quick automation. =)
 
Hi ,


To add to whatever has already been posted , there is a complete discussion of this topic here :


http://www.cpearson.com/excel/MatrixToVector.aspx


Narayan
 
@Narayan


And here I was thinking I had come up with something new. =)

Actually, it looks like Chip uses OFFSET as his main function, while I used INDEX. Would be interesting to see a comparison of the speed/efficiency of the two formulas.
 
Count the number of row of data area (naming as "mydata"): =ROWS(mydata) [assume @ A6]

Count the number of column of data area (naming as "mydata"): =COLUMNS(mydata)ll [assume @ B6]


To get the row and column number matrix:

for ROW:

Any cell (Say A8): 1

Next cell (A9): =IF(COUNTIF($A$8:A8,$A$6)>=$B$6,"",IF(A8<$A$6,A8+1,1))

for COLUMN:

Any cell (Say B8): 1

Next cell (B9): =IF(A9<>"",IF(B8<$B$6,B8+1,1),"")


DRAG the formula as long as you wish


Now getting the data in one column where raw data are on same sheet @ start from C8

: =IF(A8="","",INDIRECT(ADDRESS(A8,B8)))


Now getting the data in one column where raw data are on different sheet which is named as "mydata" @ start from C8

:=IF(A8="","",INDEX(mydata,A8,B8))


Hope this will help. Please, correct if I am wrong


Example File:

https://docs.google.com/spreadsheet/ccc?key=0AlMNziTCmrQIdGp0R19QVnNTWGtSNjJnZVFOeFF4bmc
 
Hello, I also need help in somewhat the same way, but then again not completely:


I need to convert this

1 2 3 4

5 6 7 8


into:

1

5

2

6

3

7

4

8


And have no experience in doing this in excel
 
Hi JensT!!


Welcome to the forums!! I think you should have started a new topic for this problem. Any ways I think moderators will do the needful in this regard.


Post No. 07 on this thread (By Luke M) already answered your query :)


Regards,
 
Hi Faseeh,


sorry for the post here but have no clue of how to code in excel so really need a dummy code that will do it for me (copy/paste) and ideally one that I can extend to e.g. 500 columns.
 
You must log in or register to reply here.
Back
Top