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

Next