how to make a table to row column
Thanks a lot Kostis, I have done what ever you said but it is not
working
vezerid wrote:
Let us say that the number of columns with dates is 3. In the
subsequent formula you change the 3 to suit.
You will need an auxiliary column right before the data that you want
to produce. It must have the numbers 0, 1, etc. starting in A2 of the
target sheet.
In Sheet2!B2:
=INDEX(Sheet1!$A$2:$A$100,INT(A2/3)+1)
In Sheet2!C2 (here the number 3 is reflected in the number of columns
of B1:D1):
=INDEX(Sheet1!$B$2:$D$2,MOD(A2,3)+1)
In Sheet2!D2:
=VLOOKUP(B2,Sheet1!$A$2:$D$100,MATCH(C2,Sheet1!$B$ 1:$D$1,0),FALSE)
Copy formulas Sheet2!B2:D2 down.
HTH
Kostis Vezerides
nader wrote:
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
|