| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to make a table to row column
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
"nader" wrote in message
ps.com... 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 Nader, Use <Paste Special<Transpose to copy transposed data. Bill Ridgeway Computer Solutions |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
Thanks Bill
but I want to keep the links Bill Ridgeway wrote: "nader" wrote in message ps.com... 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 Nader, Use <Paste Special<Transpose to copy transposed data. Bill Ridgeway Computer Solutions |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
Thanks Bill
but I want to keep the links between two tables Bill Ridgeway wrote: "nader" wrote in message ps.com... 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 Nader, Use <Paste Special<Transpose to copy transposed data. Bill Ridgeway Computer Solutions |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
Sorry, now I look at it again a Pivot Table will do that. Go to
<Data<Pivot Table ... and follow the prompts. Pivot tables can be a bit tricky at first but perseverance pays. Regards. Bill Ridgeway Computer Solutions "nader" wrote in message ups.com... Thanks Bill but I want to keep the links between two tables Bill Ridgeway wrote: "nader" wrote in message ps.com... 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 Nader, Use <Paste Special<Transpose to copy transposed data. Bill Ridgeway Computer Solutions |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to make a table to row column
Nader,
In what way is it not working? If you don't find a solution you can email me to: vezerid at act dot edu. I will be going soon but I will reply tomorrow. Regards, Kostis nader wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Macro Help | Excel Discussion (Misc queries) | |||
| macro | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
Linear Mode
