transpose every 6x2 columns to rows

[needhelp0]

Hi
I need help transposing a set of data of by transposing every 6x2 columns to 2x6 rows. Here is what I am trying to do http://imgur.com/a/Yv5re

Instead of manually copy and pasting special every six rows is there a way to enter in a formula or something else where I make this process faster?

[FDibbins]

HI, welcome to the forum

Please upload your sample workbook to the forum, not all members are able - or willing - to download from file-hosting sites

[misrasomendra]

Hi,

Looking from your picture, I created a sample file with dummy data and formula solution. Have a look.

[needhelp0]

Thank you for so replying so quickly!

So I edited the index to $B$3:$C$1202 to include the whole dataset but when I drag the formula down it only goes up to 400. Anything past that is a REF! error. I attached the file as it is.

EFTransposeData.xlsx

[misrasomendra]

Hi,

It is delivering your full array values and after that the formula is generating row numbers which are outside the array limit. If you want to suppress errors wrap the formula inside an IFERROR function.

In short your total data is already transposed till row 400. Beyond that you dont have data. If you increase the data by adding more values and than increase the range in array of INDEX you will see that #Ref is replaced by values.

See the file for IFERROR eg.

[needhelp0]

Oh I got it. I completely forgot that there would be less rows once transposed . Thanks again!

[misrasomendra]

You are welcome, if you like the solution you can click the little star in my comment to give me some reps .

[needhelp0]

of course, rep given Just another quick question, I am probably going to be using this formula a lot, so could you explain how I can adjust this formula for other datasets with different amount of rows/columns?

[misrasomendra]

Hi, Sorry for the late response.

OK So, let's understand this You have a group of data which is 6 cells in height and 2 cells in width 6x2. Now you want to turn it into 2X6, i.e. for each subject there will be two rows of data with 6 cells in each row.

So for first subject we will need something like this for row 1 & row 2
Row No:1-------Row No:2------Row No.:3-------Row No.:4-------Row No.:5-------Row No.:6 ------ Row1
Row No:1-------Row No:2------Row No.:3-------Row No.:4-------Row No.:5-------Row No.:6 ------ Row 2

For next subject: we will require row no. 7,8,9,10,11,12.

So basically we need a repeating sequence of number like:
1
1
7
7
13
13
and so on for the output column 1.

Same is true for column also. we have to get a repeating sequence like 1,2,1,2,1,2, and so on.

Now INDEX Formula can give a result from an array of say MxN size if you specify which M (row number) and which N (column number) you want.
So lets take the example of subj. 1,2,3 which gives us the array of B3:C20, so we have only two column and 18 rows.

Now to get a sequence for rows in INDEX function: we used below formula (excluding =)
=(INT((ROWS($G$2:G2)-1)/2)*6)+COLUMNS($G2:G2)

Here Red 2 is number of times you want to repeat. Blue 6 is number of rows to repeat and purple COLUMNS function is adding 1 to rows.

So say you have 8 rows in the subject and 3 columns so change 2 to 3 and 6 to 8.

Now for Column argument of INDEX function below formula is used (excluding =)

=MOD(ROWS(G$2:G2)-1,2)+1
Here red 2 is number of columns. so if your data set has 4 columns change 2 to 4

Just copy the row argumnet from INDEX function of cell G2 in an cell and copy across and down to see the pattern and experiment with it. Do the same for column argument also, you will come to know what these functions are doing.

I hope this will clear your doubt. If not than write back.

[needhelp0]

Thank you for the detailed explanation it helps a lot

[misrasomendra]

Thanks for the feedback now you can mark the thread as SOLVED. & Welcome back.