• 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 a row into multiple columns

R

rfarnum

New Member
I am trying to transpose a row of data into 2 columns. Here is an example:


1 2 3 4 5 6


to


1 2

3 4

5 6


I have seen some formulas around here that were for the opposite (columns to row) but I wasn't quite able to figure out how to modify them to help me. So hopefully someone can help me!


Thanks!
 
First, here's the formula:

=INDEX($A$2:$F$2,(ROW(A1)-1)*2+(COLUMN(A1)-1)*2+MOD(COLUMN(A1),2))


Explaining the different parts:

$A$2:$F$2 ---> The range where you're getting the data from


(ROW(A1)-1)*2 ---> A type of counter. Will start at a value of 0, increments by 2 as you go copy formula down


(COLUMN(A1)-1)*2 ---> A type of counter. Will start at a value of 0, increments by 2 as you go copy formula across. Combined with the first counter, lets us increment in value whether moving down or moving across.


MOD(COLUMN(A1),2) ---> A type of switch. Will evaluate to either 1 or 0, depending on if we're in the 1st column or 2nd column. The counters will generate a sequence of numbers of {0,2,4,6,...). The switch here will add 1 to that sequence if we're in the first column, causing sequnce in first column to be (1,3,5,...) and sequence in second column to be {2,4,6,...)
 
Good day Luke M


I have looked at your formula and I have got it all wrong would appreciate it if you would have a look


https://dl.dropbox.com/u/75495784/Luke%20M%20Transpose.xlsx
 
Luke M,


The formula did exactly what I needed to do, it saved me a lot of time! Although I was having trouble applying it for a bigger dataset. For example:


1 2 3 4 5 6

7 8 9 10 11 12


to


1 2

3 4

5 6

7 8

9 10

11 12


The solution I found (I needed to stop playing with excel and get back to work haha) was to move the reference line to next row everytime and reset counter back to A1. So this question is more for curiousity.


Thanks again!
 
Good day rfarnum


I changed the reference cells to include the new range $A$2:$F$2 to $A$2:$R$2, or what ever your range is
 
Switching to having 2 rows in the input range definitely makes things tougher. It's still doable, you just have to figure out the correct pattern of counters/switches to get things to increment correctly.
 
Assuming that your data is in A2:F3 (2 rows X 6 columns)


Select same sized range A5:B10 (6 rows X 2 columns) and then type following formula:

=TRANSPOSE(A2:F3)

and do CTRL + SHIFT + ENTER.


The only issue with this formula is you need to select the same sized data!
 
Hi Luke(our all time excel hero)...


Wow...creating counter using formula in excel.....it's simply amazing.


You are really an inspiring role model for us....


excellent...
 
You must log in or register to reply here.
Back
Top