Concatenate columns

[jschiefer]

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]

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]

This gives me:

C = 14 25 36

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

[DonkeyOte]

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]

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.

Sub ConcatColumns()
'JBeaucaire  (10/23/2009)
Dim LC As Long, LR As Long, i As Long
Application.ScreenUpdating = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    For i = 1 To LC - 1
        LR = Cells(Rows.Count, i).End(xlUp).Row
        Range(Cells(1, i), Cells(LR, i)).Copy Cells(Rows.Count, LC).End(xlUp).Offset(1, 0)
    Next i

Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

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

[jschiefer]

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.

Sub ConcatColumns()
'JBeaucaire  (10/23/2009)
Dim LC As Long, LR As Long, i As Long
Application.ScreenUpdating = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    For i = 1 To LC - 1
        LR = Cells(Rows.Count, i).End(xlUp).Row
        Range(Cells(1, i), Cells(LR, i)).Copy Cells(Rows.Count, LC).End(xlUp).Offset(1, 0)
    Next i

Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

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

[hdmundt]

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.

Sub ConcatColumns()
'JBeaucaire  (10/23/2009)
Dim LC As Long, LR As Long, i As Long
Application.ScreenUpdating = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    For i = 1 To LC - 1
        LR = Cells(Rows.Count, i).End(xlUp).Row
        Range(Cells(1, i), Cells(LR, i)).Copy Cells(Rows.Count, LC).End(xlUp).Offset(1, 0)
    Next i

Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

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.

[zbor]

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

[JBeaucaire]

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]

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]

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

[JBeaucaire]

===========
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]

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

[JBeaucaire]

Feel free to attach a sample workbook with the macro installed and not working, I'll take a look.

[rsheer]

Im sorry. First time posting and of course I did it in the wrong place. Please forgive me