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

[Keyur]

If your data is in columns A and B then in C enter

=A2 &" "& B2

fill down till the end of your table. copy just this new data. then right click where you wanted your transposed data. Go Paste Special. Select 'Values' and check 'Transpose'.

You will have it in one row.

Hope this is what u were looking for

Keyur

[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
"BK" <[email protected]> wrote in message
news:[email protected]...
>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!!

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

[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.

"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" <[email protected]> wrote in message
> news:[email protected]...
> >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


On Tue, 3 May 2005 05:27:02 -0700, "BK" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.

"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 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
>
>
> On Tue, 3 May 2005 05:27:02 -0700, "BK" <[email protected]> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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




"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" <[email protected]> wrote in message
> news:[email protected]...
> >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!!
>
>
>