Can someone explain how the below formula to transpose all rows to one column works?

[antman2988]

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

 

[Eric W]

Break it into parts. I assume you're putting the formula in Z1 and dragging down.

	Z	AA	AB	AC

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Z1[/TH]
[TD="align: left"]=INDEX( MyData,INT((ROW()-ROW( $Z$1 ))/COLUMNS( MyData ))+1,MOD(ROW()-ROW( $Z$1 ),COLUMNS( MyData ))+1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA1[/TH]
[TD="align: left"]=ROW()-ROW($Z$1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AB1[/TH]
[TD="align: left"]=INT((ROW()-ROW( $Z$1 ))/COLUMNS( MyData ))+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AC1[/TH]
[TD="align: left"]=MOD(ROW()-ROW( $Z$1 ),COLUMNS( MyData ))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]MyData[/TH]
[TD="align: left"]=Sheet1!$F$3:$M$10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the AA1 formula in and drag down. As you can see, it just counts up from 0. Subtracting the starting row from the current row does that.

Put the AB1 formula in and drag down. When you divide the number in AA by the number of columns in your table (my sample table has 8), and drop the remainder (INT), you get a counter that only increments by 1 for every row in your table.

Put the AC1 formula in and drag down. The MOD function is equivalent to dividing by the number of columns and taking the remainder, then we add 1. So this pattern repeats 1-8 over and over.

Then finally INDEX uses the first number (AB) as an index into your table for the row, and uses the second number (AC) as an index for the column.

Hope this helps.