Excel Help for Concatenate Two Columns?

Excel Help for Concatenate Two Columns? in Developing and Auditing Analytical Models

Concatenate Two Columns?

Rate this:

(3/5 from 1 vote)

	Hi, I don't know whether this is the correct place to post my doubt. I am new to this forum. i have a doubt about concatenation. I have two columns with set of words in it. I want to make all the combinations by using those words. For an example, If I have the following words in column A, Alabama Alaska Arizona Texas Washington And in column B, Hotel Theatre If I combine both the columns, I want the output like the following Alabama Hotel Alaska Hotel Arizona Hotel Texas Hotel Washington Hotel Alabama Theatre Alaska Theatre Arizona Theatre Texas Theatre Washington Theatre What is the function i have to use to get this result? Explain me the procedures. I can't do anything manually. Because i have 40 keywords in column A and 15 keywords in column B. I have used the concatenation function already. It takes more time to do. So, i can't use that for getting bulk combinations. Explain me If you have any idea to get the result. Thanks.
	Uma
	Posted by uma on 19 Apr 2006

Replies - Displaying 1 to 10 of 13

Order Replies By: Most Recent | Chronological | Highest Rated

Concatenate two columns with VBA loop

Rate this:

(2.5/5 from 2 votes)

The easiest and most flexible way to concatenate two columns of text into a list of all possible combinations is to do it via a VBA macro.

If, for example, you have the following spreadsheet:
Excel Spreadsheet:

	A	B	C	D
1	Alabama	Hotel
2	Alaska	Theatre
3	Arizona
4	Texas
5	Washington

You could then run the following macro to concatenate each possible combination of city and site in column D:
VBA Code:

Sub combine_columns()
Dim town As Variant
Dim site As Variant
Range("D1").Select 'Change as appropriate
For Each site In Range("B1:B2") 'Change as appropriate
    For Each town In Range("A1:A5") 'Change as appropriate
        ActiveCell.Formula = town & " " & site
        ActiveCell.Offset(1, 0).Select
    Next
Next
Range("D1").Select
End Sub

Try opening your VBA editor from Tools >> Macro >> Visual Basic Editor, insert a new module by choosing Insert >> Module and paste the above code in. The macro will now be available from Tools >> Macro >> Macros.

Excel Business Forums Administrator

Posted by Excel Helper on 20 Apr 2006

	Works Great, But... Rate this: (3/5 from 1 vote) I use the concatenate function to help build out lengthy keyword lists, and this macro works like a charm. However, it would be great if I didn't have to edit the cell ranges in the macro each time I use it. Some times I have 3 items in column A and other times I have 30. Does anyone know of a way to make the ranges in this macro more dynamic?
	www.ProjectMatt.com
	Posted by ProjectMatt on 29 Nov 2006

Dynamically get end of columns

Rate this:

(3/5 from 1 vote)

We can dynamicallly get the end of a column to make the selections with a modification to the VBA code such as:

VBA Code:

Range("A1", Range("A1").End(xlDown)).Select

Excel Business Forums Administrator

Posted by Excel Helper on 29 Nov 2006

	Thank You ! Rate this: (3/5 from 3 votes) I added the new VBA code, and it works like a charm. Thanks again!
	www.ProjectMatt.com
	Posted by ProjectMatt on 30 Nov 2006

Problems with single words

Rate this:

(3/5 from 1 vote)

I am using the following VBA code to concatenate two columns of data:
VBA Code:

Sub Combine_Columns()
Dim modifier As Variant
Dim word As Variant

Range("F4").Select 'Change as appropriate

For Each word In Range("D4", Range("D4").End(xlDown))
    For Each modifier In Range("B4", Range("B4").End(xlDown))
        ActiveCell.Formula = modifier & " " & word
        ActiveCell.Offset(1, 0).Select
Next
Next
Range("F4").Select

End Sub

This works great as long as there is more than 1 word in each column; However, when there is only one word in either column the macro will run indefinitely (combining words with blank cells). I've tried fixing this using a series of If statements, but I still can't get it to work (or at the very least prompt the user that more than 2 words must be used)

Any help that you can offer would be much appreciated.

www.ProjectMatt.com

Posted by ProjectMatt on 15 May 2007

Test for range first

Rate this:

(3/5 from 1 vote)

One way to test for the range is to first check if there is only one cell.

VBA Code:

Range("B1").Select

If ActiveCell.Offset(1, 0) <> "" Then
Range(Selection, Selection.End(xlDown)).Select
End If

For Each word In Selection
'original code here
Next

Excel Business Forums Administrator

Posted by Excel Helper on 15 May 2007

	Thanks Again! Rate this: (3/5 from 1 vote) You've come to the rescue yet again Russell. Thanks so much!
	www.ProjectMatt.com
	Posted by ProjectMatt on 16 May 2007

	Concatenate 4 columns? Rate this: (2.5/5 from 2 votes) Hi, you have been addressing the exact same issues I am trying to figure out, except I need to do this for 4 columns, where some have 10 rows and others have 40 rows . . .? Any suggestions, any help would be greatly appreciated. Thanks.
	Posted by keybiscayner on 24 May 2007

	Excel and permutations for many columns Rate this: (3.5/5 from 2 votes) There exists an excel add in: DigDB7 Works great. Need to purchase after 15 days.
	Posted by keybiscayner on 09 Jun 2007

	Embed more For...Next loops Rate this: (3/5 from 1 vote) To concatenate more than 2 columns you can use the same method except that you will need to include more For..Next loops in the code for each column.
	Excel Business Forums Administrator
	Posted by Excel Helper on 11 Jun 2007

Displaying page 1 of 2

Find relevant Excel templates and add-ins for Concatenate two columns? in the Excel Business Solutions Directory

Concatenate two columns?

Excel Help for Concatenate Two Columns? in Developing and Auditing Analytical Models

Concatenate Two Columns?