Forum Discussion
Can someone explain how the below formula to transpose all rows to one column works?
Hello,
I have used this formula: **=INDEX( MyData,INT((ROW()-ROW( $Z$1 ))/COLUMNS( MyData ))+1,MOD(ROW()-ROW( $Z$1 ),COLUMNS( MyData ))+1)** to transpose all of my rows of data into one column. Unfortunately, I can't entirely wrap my head around how it works.
Would someone be willing to explain it to me? Any assistance is appreciated.
Thanks,
Anthony Smith
I'll do my best.. its a quite lovely formula.
INDEX takes returns the contents of a cell from a specific location in a range. For example what is the 4th item in the range A1:A7 ( =index(A1:A7,4) ) You can then use the optional second variable to specify a column as well. As in what is in the cell that is 4 rows down, and 3 columns over. ( =index(A1:G7,4,3) )
Row() - row($Z$1) is simply counting +1 for every row down a column the formula is in. If you just add this bit of the formula to a work book and drag it down you will get 1, 2, 3, 4, 5, 6 etc.
Now if you divide that by the number of columns you have which is what the /Columns(myData)+1 you will get something like this:
1
1.25
1.5
1.75
2
2.15
etc.. (this is for a 4 column table) ie (1/4)+1 = 1.25, (2/2)+1 = 1.5.
Then all of that is wrapped up in INT ( ) which rounds down to the nearest whole number.
The end result is that this part of the formula gives you something like 1,1,1,1,2,2,2,2,3,3,3,3 This is in the INDEX as the row number.
Next up in the MOD which is used in the INDEX as the column variable. Here we are getting the same count from row()-row(z1) but now dividing it by the number of the columns and getting the remainder which will look like 0,1,2,3. We then add 1 to get 1,2,3,4 which makes it usable in the index.
After all that what you end up with is something that looks like this INDEX(myData,1,1) in row 1, in row 2 you have INDEX(myData, 1, 2).. on the fifth row it would then go INDEX(MyData, 2, 1) and carry on until you run out of rows and then you will get an error.
Anyway, hope that helps.. at least a little bit.
I'll do my best.. its a quite lovely formula.
INDEX takes returns the contents of a cell from a specific location in a range. For example what is the 4th item in the range A1:A7 ( =index(A1:A7,4) ) You can then use the optional second variable to specify a column as well. As in what is in the cell that is 4 rows down, and 3 columns over. ( =index(A1:G7,4,3) )
Row() - row($Z$1) is simply counting +1 for every row down a column the formula is in. If you just add this bit of the formula to a work book and drag it down you will get 1, 2, 3, 4, 5, 6 etc.
Now if you divide that by the number of columns you have which is what the /Columns(myData)+1 you will get something like this:
1
1.25
1.5
1.75
2
2.15
etc.. (this is for a 4 column table) ie (1/4)+1 = 1.25, (2/2)+1 = 1.5.
Then all of that is wrapped up in INT ( ) which rounds down to the nearest whole number.
The end result is that this part of the formula gives you something like 1,1,1,1,2,2,2,2,3,3,3,3 This is in the INDEX as the row number.
Next up in the MOD which is used in the INDEX as the column variable. Here we are getting the same count from row()-row(z1) but now dividing it by the number of the columns and getting the remainder which will look like 0,1,2,3. We then add 1 to get 1,2,3,4 which makes it usable in the index.
After all that what you end up with is something that looks like this INDEX(myData,1,1) in row 1, in row 2 you have INDEX(myData, 1, 2).. on the fifth row it would then go INDEX(MyData, 2, 1) and carry on until you run out of rows and then you will get an error.
Anyway, hope that helps.. at least a little bit.
