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

	Developing possible combinations using concatenate Rate this: (3/5 from 1 vote) I really need some assistance to develop an analysis system using excel for my research. I was wondering whether anybody can assist me here. My sets of data are normally tabulated in the following manner (either a 6 by 6 table or 4 by 4 table) Excel Spreadsheet:   A B C D E F 1  a b  c  d  e  f  2  g h  i  j  k  l  3  m n  o  p  q  r  4  s t  u  v  w  x  5  y z   1 2  3  4  6  5 6  7  8  9  0  I need to tabulate the possible 6 digit combination using the 6*6 table above. The numbers cell that for the combination has to be link. I.e:- iouty6 or ahnu12 or 94xqjd. Does any one know how to develop the above in excel sheet?  I am not an expert in VBA. So if you can show me how it is done step-by-step, I would appreciate it very much. I can also me contacted through email at [email protected]. thank you
	suthesh
	Posted by sutheshk on 25 Feb 2008

	Variable ranges for two collumns Rate this: (3/5 from 1 vote) Hello-- I found this forum on a google search and this thread is EXACTLY what I was trying to do. I have a problem with the syntax of combining the variable collumn size Range("A1", Range("A1").End(xlDown)).Select within the original solution. Each solution I try gives an error. Could anyone help?
	Posted by craigs on 29 Jan 2008

Problem testing for range first Rate this: (3/5 from 1 vote) Hi, I´m new to VBA and this forum and I´ve managed to get this macro to work for me. Until I added Russells extra bit of code to test for range first. I see it working away but it overwrites the results in cell F1. The code I´m using is ... VBA Code: Sub combine_columns() Dim columnC As Variant Dim columnB As Variant Dim columnA As Variant Range("A1").Select If ActiveCell.Offset(1, 0) <> "" Then        Range(Selection, Selection.End(xlDown)).Select End If For Each columnA In Selection Range("B1").Select If ActiveCell.Offset(1, 0) <> "" Then        Range(Selection, Selection.End(xlDown)).Select End If For Each columnB In Selection Range("C1").Select If ActiveCell.Offset(1, 0) <> "" Then        Range(Selection, Selection.End(xlDown)).Select End If For Each columnC In Selection                  Range("F1").Select 'Output column         ActiveCell.Formula = columnA & " " & columnB & " " & columnC         ActiveCell.Offset(1, 0).Select          Range("F1").Select 'Output column Next Next Next End Sub Any help would be gratefully received although it will have to be spelled out for me as I´m definitely not a natural VBAer. p.s. A very minor point is that if one column is left blank the macro still works but leaves a double space between the two terms. Impossible for me to solve.

Posted by Massimo on 08 Aug 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

	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

	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

	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

	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

	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

	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

Displaying page 1 of 2

Next