A Simple Formula/Helper Column To Transpose a Range

Try

F2

Formula:

=INDEX(CHOOSE(MOD(ROWS($A$1:A1)-1,3)+1,$A$2:$A$6,$B$2:$B$6,$C$2:$C$6),INT((ROWS($A$1:A1)-1)/3)+1)

Perhaps

F2

Formula:

IF(INDEX(CHOOSE(MOD(ROWS($A$1:A1)-1,3)+1,$A$2:$A$6,$B$2:$B$6,$C$2:$C$6),INT((ROWS($A$1:A1)-1)/3)+1)=0,INDEX(CHOOSE(MOD(ROWS($A$1:A2)-1,3)+1,$A$2:$A$6,$B$2:$B$6,$C$2:$C$6),INT((ROWS($A$1:A2)-1)/3)+1),INDEX(CHOOSE(MOD(ROWS($A$1:A1)-1,3)+1,$A$2:$A$6,$B$2:$B$6,$C$2:$C$6),INT((ROWS($A$1:A1)-1)/3)+1))

Originally Posted by chullan88

hi Ankur,

In the place of blank cell,it shows the next value.

Also the formula has become very long

Just for curiosity, can we do it using a couple of helper columns?


Regards

Perhaps not , I did what I can..... Sorry

Two questions. I need you to answer BOTH questions.

Are the values in column C real numbers that you will add/subtract/multiply/divide later on, or are they labels?

Can a value of zero exist anywhere in the Table?

Originally Posted by chullan88

Ans.1) Column C is part of the range and I require these values to be part of the table.

I don't think your first response actually answers Glenn's question:

Are the values in column C real numbers that you will add/subtract/multiply/divide later on, or are they labels?

In other words, once you have transposed these values, are you then going to want to use them further in other formulae where they may be added, subtracted, etc.?

Unfortunately, you did not answer EITHER of my questions...

Never mind. let's try to get the answer another way.

to create a helper column in F2, copied down:
=IFERROR(INDEX($A$2:$C$6,INT((ROWS($1:1)-1)/3)+1,MOD(ROWS($1:1)-1,3)+1)&"","")

to remove blanks in G2, an array formula, copied down:
=IFERROR(INDEX(F:F,SMALL(IF($F$2:$F$18<>"",ROW($F$2:$F$18)),ROWS($G$2:G2))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Q1 (again). The numerical values from column C are now text that looks like a number. Is that a problem for you?


Q2 is no longer important - forget it!!

OK!!

So. The formula is INDEX(Range, row number, column number)


range is the rectangle containing your data (A2 to C6).

To go from a 2D array to a 1D array, you need to return (going down):
row 1, column 1 of the array
row 1, column 2 of the array
row 1, column 3 of the array
row 2, column 1 of the array
row 1, column 2 of the array
row 1, column 3 of the array, etc


The piece of formula in the columns bit is MOD(ROWS($1:1)-1,3)+1 So, put =MOD(ROWS($1:1)-1,3)+1 in a cell and drag it down. it returns 1,2,3,1,2,3,etc.

The piece of formula in the rows bit is INT((ROWS($1:1)-1)/3)+1 So, put =INT((ROWS($1:1)-1)/3)+1 in a cell and drag it down. it returns 1,1,1,2,2,2,3,3,3,etc

taken together it moves across each row and then on to the next row, etc.