merging and formating data

I have data in 2 columns that i need to merge.

IE:
A B
234 245

needs to be:
234245

Now i know i can concantenate the data but then i'm stuck.

The data can be of any number of rows, so i need to select only the cells
with data in them. I then have to take the data and transpose it.

IE:
A
234
456
689

needs to be:
A B C
234 456 689.

This way i can save it as comma delimited and import it into another source.
Basically i need my ending data to be 123,345,677,8984.

Any ideas on how to get this all done? I would like to make it a macro so i
dont have to manually do it each time. thanks.

Daniel.

Without transposing.......

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Will ignore blanks in the selected range.


Gord Dibben Excel MVP

On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" <[email protected]> wrote:

>I have data in 2 columns that i need to merge.
>
>IE:
>A B
>234 245
>
>needs to be:
>234245
>
>Now i know i can concantenate the data but then i'm stuck.
>
>The data can be of any number of rows, so i need to select only the cells
>with data in them. I then have to take the data and transpose it.
>
>IE:
>A
>234
>456
>689
>
>needs to be:
>A B C
>234 456 689.
>
>This way i can save it as comma delimited and import it into another source.
>Basically i need my ending data to be 123,345,677,8984.
>
>Any ideas on how to get this all done? I would like to make it a macro so i
>dont have to manually do it each time. thanks.
>

Thanks, The problem with this is i have to select every cell one at a time.

I have 40 plus rows that i need joined. This macro forces me to do this 40
plus times. Any ideas on fixing this?


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Daniel.
>
> Without transposing.......
>
> Sub ConCat_Cells()
> Dim x As Range
> Dim y As Range
> Dim z As Range
> Dim w As String
> Dim sbuf As String
> On Error GoTo endit
> w = InputBox("Enter the Type of De-limiter Desired")
> Set z = Application.InputBox("Select Destination Cell", _
> "Destination Cell", , , , , , 8)
> Application.SendKeys "+{F8}"
> Set x = Application.InputBox _
> ("Select Cells...Contiguous or Non-Contiguous", _
> "Cells Selection", , , , , , 8)
> For Each y In x
> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
> Next
> z = Left(sbuf, Len(sbuf) - 1)
> Exit Sub
> endit:
> MsgBox "Nothing Selected. Please try again."
> End Sub
>
> Will ignore blanks in the selected range.
>
>
> Gord Dibben Excel MVP
>
> On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" <[email protected]>
> wrote:
>
>>I have data in 2 columns that i need to merge.
>>
>>IE:
>>A B
>>234 245
>>
>>needs to be:
>>234245
>>
>>Now i know i can concantenate the data but then i'm stuck.
>>
>>The data can be of any number of rows, so i need to select only the cells
>>with data in them. I then have to take the data and transpose it.
>>
>>IE:
>>A
>>234
>>456
>>689
>>
>>needs to be:
>>A B C
>>234 456 689.
>>
>>This way i can save it as comma delimited and import it into another
>>source.
>>Basically i need my ending data to be 123,345,677,8984.
>>
>>Any ideas on how to get this all done? I would like to make it a macro so
>>i
>>dont have to manually do it each time. thanks.
>>
>

Daniel

I guess I don't understand your layout.

First you state you want Column A 234 joined with column B 245 to return
234245

This can be done with =A1 & B1 entered in C1 to give 234245 per your example
and dragged down

Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689 and
you want them in a row as 234 456 689 so youi save as comma-delimited.

For that step, select column A and run my macro.

You will get 234,456,689 in one cell of your choice.

Now........What happened to column B? Where does your first example fit with
your second example?


Gord



On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" <[email protected]> wrote:

>Thanks, The problem with this is i have to select every cell one at a time.
>
>I have 40 plus rows that i need joined. This macro forces me to do this 40
>plus times. Any ideas on fixing this?
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:[email protected]...
>> Daniel.
>>
>> Without transposing.......
>>
>> Sub ConCat_Cells()
>> Dim x As Range
>> Dim y As Range
>> Dim z As Range
>> Dim w As String
>> Dim sbuf As String
>> On Error GoTo endit
>> w = InputBox("Enter the Type of De-limiter Desired")
>> Set z = Application.InputBox("Select Destination Cell", _
>> "Destination Cell", , , , , , 8)
>> Application.SendKeys "+{F8}"
>> Set x = Application.InputBox _
>> ("Select Cells...Contiguous or Non-Contiguous", _
>> "Cells Selection", , , , , , 8)
>> For Each y In x
>> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
>> Next
>> z = Left(sbuf, Len(sbuf) - 1)
>> Exit Sub
>> endit:
>> MsgBox "Nothing Selected. Please try again."
>> End Sub
>>
>> Will ignore blanks in the selected range.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" <[email protected]>
>> wrote:
>>
>>>I have data in 2 columns that i need to merge.
>>>
>>>IE:
>>>A B
>>>234 245
>>>
>>>needs to be:
>>>234245
>>>
>>>Now i know i can concantenate the data but then i'm stuck.
>>>
>>>The data can be of any number of rows, so i need to select only the cells
>>>with data in them. I then have to take the data and transpose it.
>>>
>>>IE:
>>>A
>>>234
>>>456
>>>689
>>>
>>>needs to be:
>>>A B C
>>>234 456 689.
>>>
>>>This way i can save it as comma delimited and import it into another
>>>source.
>>>Basically i need my ending data to be 123,345,677,8984.
>>>
>>>Any ideas on how to get this all done? I would like to make it a macro so
>>>i
>>>dont have to manually do it each time. thanks.
>>>
>>
>

Gord,

Thanks for the explination. I went back and looked at your code. I was not
using it right. I do need column A and B added in C with =A1&B1. After this
is done i need to take all of the data in column C and have it comma
delimited.

I think i can get there from here. Thanks for the help!

daniel.


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Daniel
>
> I guess I don't understand your layout.
>
> First you state you want Column A 234 joined with column B 245 to return
> 234245
>
> This can be done with =A1 & B1 entered in C1 to give 234245 per your
> example
> and dragged down
>
> Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689
> and
> you want them in a row as 234 456 689 so youi save as comma-delimited.
>
> For that step, select column A and run my macro.
>
> You will get 234,456,689 in one cell of your choice.
>
> Now........What happened to column B? Where does your first example fit
> with
> your second example?
>
>
> Gord
>
>
>
> On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" <[email protected]>
> wrote:
>
>>Thanks, The problem with this is i have to select every cell one at a
>>time.
>>
>>I have 40 plus rows that i need joined. This macro forces me to do this 40
>>plus times. Any ideas on fixing this?
>>
>>
>>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>>news:[email protected]...
>>> Daniel.
>>>
>>> Without transposing.......
>>>
>>> Sub ConCat_Cells()
>>> Dim x As Range
>>> Dim y As Range
>>> Dim z As Range
>>> Dim w As String
>>> Dim sbuf As String
>>> On Error GoTo endit
>>> w = InputBox("Enter the Type of De-limiter Desired")
>>> Set z = Application.InputBox("Select Destination Cell", _
>>> "Destination Cell", , , , , , 8)
>>> Application.SendKeys "+{F8}"
>>> Set x = Application.InputBox _
>>> ("Select Cells...Contiguous or Non-Contiguous", _
>>> "Cells Selection", , , , , , 8)
>>> For Each y In x
>>> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
>>> Next
>>> z = Left(sbuf, Len(sbuf) - 1)
>>> Exit Sub
>>> endit:
>>> MsgBox "Nothing Selected. Please try again."
>>> End Sub
>>>
>>> Will ignore blanks in the selected range.
>>>
>>>
>>> Gord Dibben Excel MVP
>>>
>>> On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" <[email protected]>
>>> wrote:
>>>
>>>>I have data in 2 columns that i need to merge.
>>>>
>>>>IE:
>>>>A B
>>>>234 245
>>>>
>>>>needs to be:
>>>>234245
>>>>
>>>>Now i know i can concantenate the data but then i'm stuck.
>>>>
>>>>The data can be of any number of rows, so i need to select only the
>>>>cells
>>>>with data in them. I then have to take the data and transpose it.
>>>>
>>>>IE:
>>>>A
>>>>234
>>>>456
>>>>689
>>>>
>>>>needs to be:
>>>>A B C
>>>>234 456 689.
>>>>
>>>>This way i can save it as comma delimited and import it into another
>>>>source.
>>>>Basically i need my ending data to be 123,345,677,8984.
>>>>
>>>>Any ideas on how to get this all done? I would like to make it a macro
>>>>so
>>>>i
>>>>dont have to manually do it each time. thanks.
>>>>
>>>
>>
>

Yep.

First combine A and B in C then paste values.

Run the macro and select all of column C to give a comma delimited string of
all column C in one cell.


Gord

On Sun, 20 Feb 2005 05:22:01 GMT, "Daniel M" <[email protected]> wrote:

>Gord,
>
>Thanks for the explination. I went back and looked at your code. I was not
>using it right. I do need column A and B added in C with =A1&B1. After this
>is done i need to take all of the data in column C and have it comma
>delimited.
>
>I think i can get there from here. Thanks for the help!
>
>daniel.
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:[email protected]...
>> Daniel
>>
>> I guess I don't understand your layout.
>>
>> First you state you want Column A 234 joined with column B 245 to return
>> 234245
>>
>> This can be done with =A1 & B1 entered in C1 to give 234245 per your
>> example
>> and dragged down
>>
>> Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689
>> and
>> you want them in a row as 234 456 689 so youi save as comma-delimited.
>>
>> For that step, select column A and run my macro.
>>
>> You will get 234,456,689 in one cell of your choice.
>>
>> Now........What happened to column B? Where does your first example fit
>> with
>> your second example?
>>
>>
>> Gord
>>
>>
>>
>> On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" <[email protected]>
>> wrote:
>>
>>>Thanks, The problem with this is i have to select every cell one at a
>>>time.
>>>
>>>I have 40 plus rows that i need joined. This macro forces me to do this 40
>>>plus times. Any ideas on fixing this?
>>>
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>>>news:[email protected]...
>>>> Daniel.
>>>>
>>>> Without transposing.......
>>>>
>>>> Sub ConCat_Cells()
>>>> Dim x As Range
>>>> Dim y As Range
>>>> Dim z As Range
>>>> Dim w As String
>>>> Dim sbuf As String
>>>> On Error GoTo endit
>>>> w = InputBox("Enter the Type of De-limiter Desired")
>>>> Set z = Application.InputBox("Select Destination Cell", _
>>>> "Destination Cell", , , , , , 8)
>>>> Application.SendKeys "+{F8}"
>>>> Set x = Application.InputBox _
>>>> ("Select Cells...Contiguous or Non-Contiguous", _
>>>> "Cells Selection", , , , , , 8)
>>>> For Each y In x
>>>> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
>>>> Next
>>>> z = Left(sbuf, Len(sbuf) - 1)
>>>> Exit Sub
>>>> endit:
>>>> MsgBox "Nothing Selected. Please try again."
>>>> End Sub
>>>>
>>>> Will ignore blanks in the selected range.
>>>>
>>>>
>>>> Gord Dibben Excel MVP
>>>>
>>>> On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" <[email protected]>
>>>> wrote:
>>>>
>>>>>I have data in 2 columns that i need to merge.
>>>>>
>>>>>IE:
>>>>>A B
>>>>>234 245
>>>>>
>>>>>needs to be:
>>>>>234245
>>>>>
>>>>>Now i know i can concantenate the data but then i'm stuck.
>>>>>
>>>>>The data can be of any number of rows, so i need to select only the
>>>>>cells
>>>>>with data in them. I then have to take the data and transpose it.
>>>>>
>>>>>IE:
>>>>>A
>>>>>234
>>>>>456
>>>>>689
>>>>>
>>>>>needs to be:
>>>>>A B C
>>>>>234 456 689.
>>>>>
>>>>>This way i can save it as comma delimited and import it into another
>>>>>source.
>>>>>Basically i need my ending data to be 123,345,677,8984.
>>>>>
>>>>>Any ideas on how to get this all done? I would like to make it a macro
>>>>>so
>>>>>i
>>>>>dont have to manually do it each time. thanks.
>>>>>
>>>>
>>>
>>
>