Excel Tables - Cross tbl tp Reg tbl

[alish]

Hello, Can any one help me with my dilemma here: I am tring to convert my
"cross-table" looking table to a regular table, see below details:

"cross-tbl"
clmn A clmn B clmn C Totals 2008 Totals 2009 Totals 2010
Info1 data data $$$ $$$ $$$
Info2 data data $$$ $$$ $$$
Info3 data data $$$ $$$ $$$

I want:
clmn A clmn B clmn C colmn D Value
Info1 data data Totals 2008 $$$
Info1 data data Totals 2009 $$$
Info1 data data Totals 2010 $$$
Info2 data data Totals 2008 $$$
Info2 data data Totals 2009 $$$
Info2 data data Totals 2010 $$$
Info3 data data Totals 2008 $$$
Info3 data data Totals 2009 $$$
Info3 data data Totals 2010 $$$

Please help. I tried Pivot but it would show only once of Info and the two
datas.
Thanks in advance.

 

[Jacob Skaria]

Suppose your data starts from Row2 of sheet "cross-tbl" with Row1 having
headers..as below

Head1 Head2 Head3 2008 2009
Info1 data data 1 4
Info2 data data 2 5
Info3 data data 3 6


Try the below formula in another Sheet cell A1 and copy/drag the formula to
cells B1 and C1..Now drag down the formula as required...

=INDEX(cross-tbl!$A:$F,CEILING(ROW(A1)/3,1)+1,COLUMN(A1))

In cell D1 enter the formula and copy down as required
=INDEX(cross-tbl!$D:$F,1,MOD(ROW(A1)-1,3)+1)

In cell E1 enter the formula and copy down as required
=INDEX(cross-tbl!$D:$F,CEILING(ROW(D1)/3,1)+1,MOD(ROW(A1)-1,3)+1)

 

[alish]

Thanks, Jacob. It worked!

Suppose your data starts from Row2 of sheet "cross-tbl" with Row1 having
headers..as below

Head1 Head2 Head3 2008 2009
Info1 data data 1 4
Info2 data data 2 5
Info3 data data 3 6


Try the below formula in another Sheet cell A1 and copy/drag the formula to
cells B1 and C1..Now drag down the formula as required...

=INDEX(cross-tbl!$A:$F,CEILING(ROW(A1)/3,1)+1,COLUMN(A1))

In cell D1 enter the formula and copy down as required
=INDEX(cross-tbl!$D:$F,1,MOD(ROW(A1)-1,3)+1)

In cell E1 enter the formula and copy down as required
=INDEX(cross-tbl!$D:$F,CEILING(ROW(D1)/3,1)+1,MOD(ROW(A1)-1,3)+1)