Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
Dear Sir
Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
Try this:
=OFFSET(A$1,MOD(ROWS(A$1:A1)-1,n),INT((ROWS(A$1:A1)-1)/n)) Where n = number of rows in your table. In your case n = 3 Biff "Mr. Low" wrote in message ... Dear Sir Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
You could try this in A7 and copy it down to A18:
=INDEX(A$1:D$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)-1) Hope this helps. Pete On Mar 1, 6:23 pm, Mr. Low wrote: Dear Sir Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
Hello T. Valko,
Great formula. It works perfectly well. Many thanks Low -- A36B58K641 "T. Valko" wrote: Try this: =OFFSET(A$1,MOD(ROWS(A$1:A1)-1,n),INT((ROWS(A$1:A1)-1)/n)) Where n = number of rows in your table. In your case n = 3 Biff "Mr. Low" wrote in message ... Dear Sir Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
You're welcome. Thanks for the feedback!
Biff "Mr. Low" wrote in message ... Hello T. Valko, Great formula. It works perfectly well. Many thanks Low -- A36B58K641 "T. Valko" wrote: Try this: =OFFSET(A$1,MOD(ROWS(A$1:A1)-1,n),INT((ROWS(A$1:A1)-1)/n)) Where n = number of rows in your table. In your case n = 3 Biff "Mr. Low" wrote in message ... Dear Sir Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Any formula to tranpose columns into rows ?
Dear Pete,
I do not have any problem in using =INDEX(A$1:D$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)-1) to get the correct answer from 4 column X 3 rows table earlier on. However when I modify the formula to =INDEX(A$1:D$12,MOD(ROW()-1,12)+1,INT((ROW()-1)/12)-1) for the 4 column x 12 rows table, I could not get the answer right. May I know why ? llustrated example: A B C D 1 12.00 26.00 78.00 88.00 2 21.00 14.00 65.00 52.00 3 32.00 61.00 42.00 32.00 4 66.00 70.00 22.00 10.00 5 31.00 41.00 21.00 9.00 6 10.00 33.00 30.00 6.00 7 44.00 54.00 8.00 46.00 8 38.00 10.00 7.00 64.00 9 77.00 92.00 13.00 52.00 10 99.00 22.00 31.00 25.00 11 64.00 11.00 43.00 31.00 12 55.00 9.00 39.00 80.00 Transposed data 19 44.00 20 38.00 21 77.00 22 99.00 23 64.00 24 55.00 25 12.00 26 21.00 27 32.00 28 66.00 29 31.00 30 10.00 Thanks Low -- A36B58K641 "Pete_UK" wrote: You could try this in A7 and copy it down to A18: =INDEX(A$1:D$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)-1) Hope this helps. Pete On Mar 1, 6:23 pm, Mr. Low wrote: Dear Sir Lets consider the following worksheet: A B C D 1 12 26 78 88 2 21 14 65 52 3 32 61 42 31 7 12 8 21 9 32 10 26 11 14 12 61 13 78 14 65 15 42 16 88 17 52 18 31 May I know if you have a formula to tranpose content in cell A1: D3 to a single Column A ( A7: A18) as ilustrated above ? Any formula to input at cell A7 and copy down to get the straight answer ? Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 - link columns across workgroups | Excel Discussion (Misc queries) | |||
Can I hide rows in Excel 2002 using a formula in a cell? | Excel Discussion (Misc queries) | |||
Excel 2002 Unhide Columns Shortcut?? | Excel Discussion (Misc queries) | |||
I cannot delete rows or columns in Excel 2002. How do I fix this? | Setting up and Configuration of Excel | |||
Open txt file with more than 256 columns (how to tranpose in rows)? | Excel Discussion (Misc queries) |