Is it possible to transpose 2 columns of data into 1 row?

[BK]

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!!

 

[Otto Moehrbach]

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

 

[McHez]

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]

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.

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

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!!

 

[Gord Dibben]

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

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.

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

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!!

 

[BK]

Many Thanks Gord! This website is very helpful.

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

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.

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
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!!

 

[BK]

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

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

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!!