Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to convert/transpose columns to rows (and vice versa)
Hello everybody,
I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
#2
|
|||
|
|||
Hi
not sure if it fits the description of not too complex and doesn't work by dragging, but there is a transpose function say you have .........A..........B 1.....Cat........10 2.....Dog.......15 3....Rat..........20 and you want to transpose this table to the range A5:C6 select A5:C6 type =TRANSPOSE(A1:B3) and then press Control & Shift & Enter - not just Enter and the data will be transposed. Cheers JulieD "markx" wrote in message ... Hello everybody, I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
#3
|
|||
|
|||
Thanks Julie,
In fact, I've tested this function just before, but as you pointed it out, it doesn't work by dragging it. I remember that some time ago, I've found somewhere on the net two functions that work as I would like, but concern only transposing rows to columns. One involves INDIRECT, ADDRESS and ROW functions, the other goes with INDEX and ROW function. Unfortunately I can't find them back right now to quote them directly. While searching, I've just found something similar on the groups, I post it here for the illustration purposes, maybe this could help you imagine a "general" formula covering my problem (unfortunately I'm unable to do it by myself:-(: =INDEX(Sheet1!$1:$1,ROW()*2-1) = this one is supposed to skip every second value, still row to column or =INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) = still row to column, a particular case or =INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1) ;4);"1";"")&"38") = still row to column, same post as before Thanks once again for your help and FU on this! Mark --------------------- "JulieD" wrote in message ... Hi not sure if it fits the description of not too complex and doesn't work by dragging, but there is a transpose function say you have ........A..........B 1.....Cat........10 2.....Dog.......15 3....Rat..........20 and you want to transpose this table to the range A5:C6 select A5:C6 type =TRANSPOSE(A1:B3) and then press Control & Shift & Enter - not just Enter and the data will be transposed. Cheers JulieD "markx" wrote in message ... Hello everybody, I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
#4
|
|||
|
|||
Say you have data from A1 thru B4 and you want this data transposed from
cells D5 thru G6 on cells D5 type =OFFSET(INDIRECT(ADDRESS(1,ROW()-4)),COLUMN()-4,0) and copy it across and down "markx" wrote in message ... Thanks Julie, In fact, I've tested this function just before, but as you pointed it out, it doesn't work by dragging it. I remember that some time ago, I've found somewhere on the net two functions that work as I would like, but concern only transposing rows to columns. One involves INDIRECT, ADDRESS and ROW functions, the other goes with INDEX and ROW function. Unfortunately I can't find them back right now to quote them directly. While searching, I've just found something similar on the groups, I post it here for the illustration purposes, maybe this could help you imagine a "general" formula covering my problem (unfortunately I'm unable to do it by myself:-(: =INDEX(Sheet1!$1:$1,ROW()*2-1) = this one is supposed to skip every second value, still row to column or =INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) = still row to column, a particular case or =INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1) ;4);"1";"")&"38") = still row to column, same post as before Thanks once again for your help and FU on this! Mark --------------------- "JulieD" wrote in message ... Hi not sure if it fits the description of not too complex and doesn't work by dragging, but there is a transpose function say you have ........A..........B 1.....Cat........10 2.....Dog.......15 3....Rat..........20 and you want to transpose this table to the range A5:C6 select A5:C6 type =TRANSPOSE(A1:B3) and then press Control & Shift & Enter - not just Enter and the data will be transposed. Cheers JulieD "markx" wrote in message ... Hello everybody, I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
#5
|
|||
|
|||
Column with values starting in A2 going down
=OFFSET($A$2,COLUMN(A:A)-1,) copy across and it will return A2, A3, A4 etc Row with values starting in D2 =OFFSET($D$2,,ROW(1:1)-1,) copy down and it will return D2, E2, F2 etc -- Regards, Peo Sjoblom "markx" wrote in message ... Thanks Julie, In fact, I've tested this function just before, but as you pointed it out, it doesn't work by dragging it. I remember that some time ago, I've found somewhere on the net two functions that work as I would like, but concern only transposing rows to columns. One involves INDIRECT, ADDRESS and ROW functions, the other goes with INDEX and ROW function. Unfortunately I can't find them back right now to quote them directly. While searching, I've just found something similar on the groups, I post it here for the illustration purposes, maybe this could help you imagine a "general" formula covering my problem (unfortunately I'm unable to do it by myself:-(: =INDEX(Sheet1!$1:$1,ROW()*2-1) = this one is supposed to skip every second value, still row to column or =INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) = still row to column, a particular case or =INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1) ;4);"1";"")&"38") = still row to column, same post as before Thanks once again for your help and FU on this! Mark --------------------- "JulieD" wrote in message ... Hi not sure if it fits the description of not too complex and doesn't work by dragging, but there is a transpose function say you have ........A..........B 1.....Cat........10 2.....Dog.......15 3....Rat..........20 and you want to transpose this table to the range A5:C6 select A5:C6 type =TRANSPOSE(A1:B3) and then press Control & Shift & Enter - not just Enter and the data will be transposed. Cheers JulieD "markx" wrote in message ... Hello everybody, I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
#6
|
|||
|
|||
Thanks a lot folks,
You are great!! "Peo Sjoblom" wrote in message ... Column with values starting in A2 going down =OFFSET($A$2,COLUMN(A:A)-1,) copy across and it will return A2, A3, A4 etc Row with values starting in D2 =OFFSET($D$2,,ROW(1:1)-1,) copy down and it will return D2, E2, F2 etc -- Regards, Peo Sjoblom "markx" wrote in message ... Thanks Julie, In fact, I've tested this function just before, but as you pointed it out, it doesn't work by dragging it. I remember that some time ago, I've found somewhere on the net two functions that work as I would like, but concern only transposing rows to columns. One involves INDIRECT, ADDRESS and ROW functions, the other goes with INDEX and ROW function. Unfortunately I can't find them back right now to quote them directly. While searching, I've just found something similar on the groups, I post it here for the illustration purposes, maybe this could help you imagine a "general" formula covering my problem (unfortunately I'm unable to do it by myself:-(: =INDEX(Sheet1!$1:$1,ROW()*2-1) = this one is supposed to skip every second value, still row to column or =INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) = still row to column, a particular case or =INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1) ;4);"1";"")&"38") = still row to column, same post as before Thanks once again for your help and FU on this! Mark --------------------- "JulieD" wrote in message ... Hi not sure if it fits the description of not too complex and doesn't work by dragging, but there is a transpose function say you have ........A..........B 1.....Cat........10 2.....Dog.......15 3....Rat..........20 and you want to transpose this table to the range A5:C6 select A5:C6 type =TRANSPOSE(A1:B3) and then press Control & Shift & Enter - not just Enter and the data will be transposed. Cheers JulieD "markx" wrote in message ... Hello everybody, I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would transpose columns to rows (or vice versa) by simple dragging of the cell. Are you aware of any, hopefully not too complex? Thanks a lot for your help on this, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Inverting rows and columns | Excel Discussion (Misc queries) | |||
How do I limit number of rows and columns on a spreadsheet | New Users to Excel | |||
Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
columns and rows | Excel Discussion (Misc queries) |