merging and formating data

  • Thread starter Thread starter Daniel M
  • Start date Start date
D

Daniel M

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
 
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?
 
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
 
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.
 
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
 
Back
Top