Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is it possible to transpose 2 columns of data into 1 row?
I have the following spreadsheet:
1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#2
|
|||
|
|||
I would use a macro something like the following. I assumed that your data
was in Columns A & B starting in A1 and that you wanted everything in Row 1. Don't forget that you have only 256 columns on the sheet. I didn't write in any error trap for running out of columns. HTH Otto Sub ShuffleData() Dim RngA As Range Dim i As Range Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngA i.Resize(, 2).Copy [A1].End(xlToRight).Offset(, 1) i.Resize(, 2).ClearContents Next i End Sub "BK" wrote in message ... I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#3
|
|||
|
|||
Here is another way, using formulae in the rows that picks up the desired
values from columns. First I assume that the numbers 1,2,3 etc are in col 1 and the letters a,b,c etc are in col 2 Then starting in col C in row 1, I enter the formula =row() in each cell. This produces an array 3,4,5 6, etc in row 2, I enter these formulae (again starting in col C) =3,=3, =C2+1, =D2+1 etc. This produces and array 3,3,4,4,5,5,6,6,7,7,8,8 etc. in row 3, I enter the formula =INDEX($A:$A,C2). This produces an array based on the values in column A, but repeating each number ie. 1,1,2,2,3,3 etc in row 4 , (starting at col D) I enter the similar formula but for column 2: =INDEX($B:$B,D2). This produces an array a,a,b,b,c,c,d,d,e,e etc. in row 5, I select either the number from row 3 or the letter from row 4, depending on whether the column is even or odd. This is done with the formula (starting at col C again): =IF(ODD(C1)=C1,C3,C4). Row 5 now has the desired array 1,a,2,b,3,c,4,d, etc (commentary - if(odd() = () is a crude way of ascertaining if a number is even or odd. There may be a better way.) "BK" wrote: I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#4
|
|||
|
|||
You assumed correctly. For this application, I will not need to use all 256
columns. I'm new to macros. Could you explain where I should put the program or kindly refer me where I can learn more about them? Thanks for your help. "Otto Moehrbach" wrote: I would use a macro something like the following. I assumed that your data was in Columns A & B starting in A1 and that you wanted everything in Row 1. Don't forget that you have only 256 columns on the sheet. I didn't write in any error trap for running out of columns. HTH Otto Sub ShuffleData() Dim RngA As Range Dim i As Range Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngA i.Resize(, 2).Copy [A1].End(xlToRight).Offset(, 1) i.Resize(, 2).ClearContents Next i End Sub "BK" wrote in message ... I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#5
|
|||
|
|||
BK
If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben Excel MVP On Tue, 3 May 2005 05:27:02 -0700, "BK" wrote: You assumed correctly. For this application, I will not need to use all 256 columns. I'm new to macros. Could you explain where I should put the program or kindly refer me where I can learn more about them? Thanks for your help. "Otto Moehrbach" wrote: I would use a macro something like the following. I assumed that your data was in Columns A & B starting in A1 and that you wanted everything in Row 1. Don't forget that you have only 256 columns on the sheet. I didn't write in any error trap for running out of columns. HTH Otto Sub ShuffleData() Dim RngA As Range Dim i As Range Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngA i.Resize(, 2).Copy [A1].End(xlToRight).Offset(, 1) i.Resize(, 2).ClearContents Next i End Sub "BK" wrote in message ... I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#6
|
|||
|
|||
Many Thanks Gord! This website is very helpful.
"Gord Dibben" wrote: BK If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben Excel MVP On Tue, 3 May 2005 05:27:02 -0700, "BK" wrote: You assumed correctly. For this application, I will not need to use all 256 columns. I'm new to macros. Could you explain where I should put the program or kindly refer me where I can learn more about them? Thanks for your help. "Otto Moehrbach" wrote: I would use a macro something like the following. I assumed that your data was in Columns A & B starting in A1 and that you wanted everything in Row 1. Don't forget that you have only 256 columns on the sheet. I didn't write in any error trap for running out of columns. HTH Otto Sub ShuffleData() Dim RngA As Range Dim i As Range Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngA i.Resize(, 2).Copy [A1].End(xlToRight).Offset(, 1) i.Resize(, 2).ClearContents Next i End Sub "BK" wrote in message ... I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
#7
|
|||
|
|||
Thanks Otto, How would I adjust this macro to transpose this set of data
(assuming data in columns A & B starting in A1, everything in Row 1) 101, abc 2215 102, abc 2406 103, ab 6 104, abc 403 I would like to have this format: 101, abc 2215, 102, abc 2406, 103, ab 6, 104, abc 403 each comma represents cell separation. THanks for you help, BK "Otto Moehrbach" wrote: I would use a macro something like the following. I assumed that your data was in Columns A & B starting in A1 and that you wanted everything in Row 1. Don't forget that you have only 256 columns on the sheet. I didn't write in any error trap for running out of columns. HTH Otto Sub ShuffleData() Dim RngA As Range Dim i As Range Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngA i.Resize(, 2).Copy [A1].End(xlToRight).Offset(, 1) i.Resize(, 2).ClearContents Next i End Sub "BK" wrote in message ... I have the following spreadsheet: 1 a 2 b 3 c 4 d I would like to have this format: 1 a 2 b 3 c 4 d Any suggestions would be greatly appreciated!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data in rows to be viewed in columns | Excel Discussion (Misc queries) | |||
Merging columns of data | Excel Discussion (Misc queries) | |||
Trying to transpose linked data using Paste Special | Excel Worksheet Functions | |||
How to get pivot table data columns instead of rows | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |