How do I use the Transpose Function
Hi there, I would like to know how o use the transpose function fro the
following table 9,400 1,661 8,861 1,302 9,489 1,376 to look like the one below 9,400 8,861 9,489 1,661 1,302 1,376 I used the function but it does not give me the result as i want it to be. Right now i have the table as below Jan Feb MP Q 9,400 9,303 A 1,661 1,470 Stretch Q 8,861 8,849 A 1,302 1,301 Fore Q 9,489 8,511 A 1,376 1,313 |
I think the transpose will not do what you want
I would suggest using the offset function for example if the Jan MP Q data were in cell d4 on sheet1 and you wanted your Jan transposed data to start in sheet2 C3 in sheet2 cell B3 I would enter Jan and in cell C3 =offset(Sheet1!D3,1,match($B3,sheet1!$D$3:$D$14,0) ) in C4 =offset(Sheet1!D3,2,match($B3,sheet1!$D$3:$D$14,0) ) in D3 =offset(Sheet1!D3,3,match($B3,sheet1!$D$3:$D$14,0) ) in D4 =offset(Sheet1!D3,4,match($B3,sheet1!$D$3:$D$14,0) ) in E3 =offset(Sheet1!D3,5,match($B3,sheet1!$D$3:$D$14,0) ) in E4 =offset(Sheet1!D3,6,match($B3,sheet1!$D$3:$D$14,0) ) (note there is only one number different in these formulas) then if you want o do the same thing for Feb with a one row spacer Enter "Feb" in B6 and copy cells C3:E4 and paste into C6:E7 "Tanweer" wrote: Hi there, I would like to know how o use the transpose function fro the following table 9,400 1,661 8,861 1,302 9,489 1,376 to look like the one below 9,400 8,861 9,489 1,661 1,302 1,376 I used the function but it does not give me the result as i want it to be. Right now i have the table as below Jan Feb MP Q 9,400 9,303 A 1,661 1,470 Stretch Q 8,861 8,849 A 1,302 1,301 Fore Q 9,489 8,511 A 1,376 1,313 |
If the functions in the reely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, you might try something like: =ArrayReshape(C2:C7,2,3,FALSE) array entered into a 2-row x 3-column range. Alan Beban Tanweer wrote: Hi there, I would like to know how o use the transpose function fro the following table 9,400 1,661 8,861 1,302 9,489 1,376 to look like the one below 9,400 8,861 9,489 1,661 1,302 1,376 I used the function but it does not give me the result as i want it to be. Right now i have the table as below Jan Feb MP Q 9,400 9,303 A 1,661 1,470 Stretch Q 8,861 8,849 A 1,302 1,301 Fore Q 9,489 8,511 A 1,376 1,313 |
All times are GMT +1. The time now is 07:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com