how to make a table to row column

[nader]

Hi,
I have a really big table with date as heading :

People 11/07 12/07 13/07
john 1 2 5
wolf 5 4 7


how can I make to be seen as:


People Date Number
john 11/07 1
john 12/07 2
john 13/07 5
wolf 11/07 5
wolf 12/07 4
wolf 13/07 7


the spreadsheet is to long so it is not possible to do it by copy and
paste, I also want to keep the link
anybody can help?
I really appreciate that
thanks
Nader

 

[Harlan Grove]

nader wrote...

I have a really big table with date as heading :

People 11/07 12/07 13/07
john 1 2 5
wolf 5 4 7

Name the original table Tbl.

how can I make to be seen as:

People Date Number
john 11/07 1
john 12/07 2
john 13/07 5
wolf 11/07 5
wolf 12/07 4
wolf 13/07 7

....

In a new worksheet enter your field names in row 1, so People in A1,
Date in B1 and Number in C1. Then enter the following formulas in row
2.

A2:
=INDEX(Tbl,2+INT((ROWS(A$2:A2)-1)/(COLUMNS(Tbl)-1)),1)

B2:
=INDEX(Tbl,1,2+MOD(ROWS(B$2:B2)-1,COLUMNS(Tbl)-1))

C2:
=INDEX(Tbl,2+INT((ROWS(A$2:A2)-1)/(COLUMNS(Tbl)-1)),2+MOD(ROWS(B$2:B2)-1,COLUMNS(Tbl)-1))

Select A2:C2 and fill down as far as needed.

 

[Jason Lepack]

How to convert a pivot table to a data sheet... using a pivot table!!

This info will do exactly what you want.
http://j-walk.com/ss/excel/usertips/tip068.htm

Cheers,
Jason Lepack

 

[nader]

GREAT!!1 Thanks alot Jason

How to convert a pivot table to a data sheet... using a pivot table!!

This info will do exactly what you want.
http://j-walk.com/ss/excel/usertips/tip068.htm

Cheers,
Jason Lepack

 

[nader]

Thank you very much, Harlan

nader wrote...

Name the original table Tbl.

...

In a new worksheet enter your field names in row 1, so People in A1,
Date in B1 and Number in C1. Then enter the following formulas in row
2.

A2:
=INDEX(Tbl,2+INT((ROWS(A$2:A2)-1)/(COLUMNS(Tbl)-1)),1)

B2:
=INDEX(Tbl,1,2+MOD(ROWS(B$2:B2)-1,COLUMNS(Tbl)-1))

C2:
=INDEX(Tbl,2+INT((ROWS(A$2:A2)-1)/(COLUMNS(Tbl)-1)),2+MOD(ROWS(B$2:B2)-1,COLUMNS(Tbl)-1))

Select A2:C2 and fill down as far as needed.