Concatenate columns

**jschiefer** · 10-22-2009, 03:56 PM

Dear forum,

I'm looking for a easy way to concatenate columns.

....A B C
1 1 4
2 2 5
3 3 6
4
5
6

I want to get the data of A and B into C:
C: 1 2 3 4 5 6

I have 123 colums of data which need to be merged into a single one.

Thanks for any help!

**NoraJohnson** · 10-22-2009, 04:01 PM

If you're looking to simply link A and B so they appear together in column C, use the following formula in C1.

=A1&B1

The & symbol will tie them together.

**jschiefer** · 10-23-2009, 03:19 AM

This gives me:

C = 14 25 36

but I want C = 1 2 3 4 5 6.

**DonkeyOte** · 10-23-2009, 03:28 AM

Are you looking to concatenate all values within 123 columns into one cell ?

What are the lengths of the strings in each cell ?

How many rows in each column ?

Are there blanks interspersed amongst the data set - if so are they to be included in the output or simply ignored ?

Post a sample file which reflects your real requirements - ie a more thorough data set, also be sure to display desired output.

"Dumbing Down" the example from real scenario is unlikely to prove beneficial in this instance...

**JBeaucaire** · 10-23-2009, 03:42 AM

If I understand what you're trying to do...create a new column that is the consolidation of all the other columns into one column in the next empty column...I would use a macro for this.

Please Login or Register  to view this content.

===========
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

**zbor** · 10-23-2009, 03:50 AM

I know that formula =CONCATENATE(A1:A19) don't work but when we compare it with other excel formulas (like =SUM(A1:A19)) I think it would be nice to implement such thing

**jschiefer** · 10-23-2009, 08:16 AM

Originally Posted by JBeaucaire

If I understand what you're trying to do...create a new column that is the consolidation of all the other columns into one column in the next empty column...I would use a macro for this.

Please Login or Register  to view this content.

===========
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Thank you, you saved me!

**JBeaucaire** · 10-23-2009, 11:42 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

**NukedWhale** · 10-23-2009, 12:30 PM

I'm a huge fan of the user defined function concrange (concatenate range). I use it all the time. Try it out, you'll love it.

Here's where you can find it:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=817

**JBeaucaire** · 10-23-2009, 12:41 PM

NukedWhale, that function (which I also use frequently) isn't related to this OPs need.

**hdmundt** · 12-01-2009, 02:30 PM

Originally Posted by JBeaucaire

If I understand what you're trying to do...create a new column that is the consolidation of all the other columns into one column in the next empty column...I would use a macro for this.

Please Login or Register  to view this content.

JBeaucaire,

I think this is a module that should work for me, but I've followed your instructions without success. I have no real knowledge of VBS, so don't get too technical with a reply if you choose to reply. (I consulted this forum a few years ago for this same situation, i.e., a need to concatenate multiple columns/rows with the resulting strings displaying in a new column. The results were exactly what I wanted.)
Was I to edit your code in some way before copying to the module? ('Something "understood" to VBA-cognicent folks, but not so for those like me)?
I made sure that my "Macro Security" setting was on Medium/Low.
My worksheet just sits there and doesn't do a thing. My worksheet is 9 columns by 60,000 rows.
Many thanks for any suggestions you care to throw my way.

**JBeaucaire** · 12-01-2009, 04:41 PM

===========
How to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select ConcatColumns from the macro list.

**hdmundt** · 12-02-2009, 10:25 AM

Thanks, but as noted above, I did follow the directions for installing the macro and it did install; ConcatColumns is in the macro list. I'll look into worksheet properties to see if I can find anything that might be blocking the command to run the macro. If you think of any likely suspects please send them my way.
Thanks, again.

hdmundt