Home > Forum Home > Developing and Auditing Analytical Models > Concatenate two columns? Share

Concatenate two columns?

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


Forum TopicPost Reply Login

Concatenate Two Columns?

Rate this:
(3/5 from 1 vote)
ConfusedHi,
 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 on
 
Replies - Displaying 11 to 13 of 13Order Replies By: Most recent | Chronological | Highest Rated
Happy
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 on
Confused
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 on
Happy
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:
 ABCD
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 on
 Displaying page 2 of 2 

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

© Business Spreadsheets   Legal   Follow us: