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 1 to 10 of 13Order Replies By: Most Recent | Chronological | Highest Rated
Confused
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 on
Oops
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 on
Confused
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 on
ConfusedI 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:
 ABCDEF
1

 a






2

 g






3

 m






4

 s






5

 y


 1




6

 5






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 on
Happy
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
Happy
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 on
Shocked
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 on
Applaud
Rate this:
(3/5 from 1 vote)
You've come to the rescue yet again Russell. Thanks so much!
 www.ProjectMatt.com
 Posted by on
Grateful
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 on
Oops
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 on
 Displaying page 1 of 2 

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