| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 73548 | 2006-10-24 11:50:00 | transforming excel row to column | bpt2 (6653) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 493681 | 2006-10-24 11:50:00 | I have a row of data in excel and I want to transpose it into a column. How do I do this? | bpt2 (6653) | ||
| 493682 | 2006-10-24 18:32:00 | Manually. :( | pcuser42 (130) | ||
| 493683 | 2006-10-24 18:42:00 | I have a row of data in excel and I want to transpose it into a column. How do I do this?Select the row, Ctrl+C to copy, select the column you want it in (you may need to move it over to fit, or put into another worksheet if there are no free columns), go Edit > Paste Special and select "Transpose" and press OK. It will paste your row as a column (or a column as a row). HTH Mike. |
Mike (15) | ||
| 493684 | 2006-10-24 23:44:00 | How about if the row includes formulae that reference cells on other worksheets. Is there a way of preserving the same cell references? | bpt2 (6653) | ||
| 493685 | 2006-10-24 23:50:00 | How about if the row includes formulae that reference cells on other worksheets. Is there a way of preserving the same cell references?Excel is usually quite clever at keeping cell references correct. EDIT: I just tried it, and it keeps all the cell references correctly. Mike. |
Mike (15) | ||
| 493686 | 2006-10-25 00:07:00 | Can you look at that again. My cell references change relative to the new location of each formula. I need them to be absolute and refer to the same cells as before | bpt2 (6653) | ||
| 493687 | 2006-10-25 00:16:00 | Can you look at that again. My cell references change relative to the new location of each formula. I need them to be absolute and refer to the same cells as beforeThen they need to be made into absolute references before you do the copy - in your formulas you highlight the cell reference you want to be absolute, and press F4. If you only want row or column absolute, continue to hit F4 until the $ appears in front of the part you want absolute. Mike. |
Mike (15) | ||
| 493688 | 2006-10-25 01:25:00 | Hi, If you don't want to change your formula references as below, I wrote a short macro to help do what you want. It worked in the simple test I gave it. Just copy the code below into a module in the spreadsheet you're working on (let me know if you don't know how to do that). Then run the macro back in Excel. The macro requests the source cells so just select the range you want to copy. Then it asks for the top left cell to paste the results to, don't select more than one cell. Also you probably won't be able to do multiple selection areas (I don't know what would happen!). HTH :) Option Explicit Public Sub PasteTransFormula() 'Sets the source range Dim rngSource As Range On Error Resume Next Set rngSource = Application.InputBox("Select the source cells to copy.", "Select Source", _ Selection.Address, , , , , 8) If Err.Number <> 0 Then MsgBox "Action cancelled", vbOKOnly, "Action cancelled" Exit Sub End If 'sets the destination range Dim rngDest As Range Set rngDest = Application.InputBox("Select the top left cell where you want to paste " & _ "the results.", "Select Destination", Selection.Address, , , , , 8) If Err.Number <> 0 Then MsgBox "Action cancelled", vbOKOnly, "Action cancelled" Exit Sub End If On Error GoTo 0 'Checks that only one cell is selected in the destination range If rngDest.Cells.Count > 1 Then MsgBox "Please select only one cell in the destination.", vbOKOnly, _ "Select one cell only" Exit Sub End If 'Loops through every row and then for each row loops through all the columns 'and pastes the formulas them in the destination location swapping columns 'for rows. Dim intRow As Integer Dim intCol As Integer For intRow = 1 To rngSource.Rows.Count For intCol = 1 To rngSource.Columns.Count rngDest.Offset(intCol - 1, intRow - 1).Formula = _ rngSource.Cells(intRow, intCol).Formula Next intCol Next intRow End Sub |
pico (4752) | ||
| 493689 | 2006-10-25 02:59:00 | Macro worked nicely thanks. | bpt2 (6653) | ||
| 1 | |||||