Concatenate two columns?

[uma]

Hi,
I am new to this forum. I have a doubt about concatenation. I have two
colums with different keywords. i have to make all the combinations by
using those words. For an example, i have the following words in the
column A.

Alabama
alaska
arizona
texas
washington

and in Column B I have the following words

hotel
theatre

i want to combine both the columns. i want the output like this.

alabama hotel
alaska hotel
arizona hotel
texas hotel
washington hotel
alabama theatre
alaska theatre
arizona theatre
texas theatre
washington theatre

What function i have to use for this? please tell me procedures. i
can't do anything manually. i have used the concatenation function
already. but i can't use that for my words. because i have 40 words in
column A and 15 words in column B. So, please tell me the possibilities
and procedures.

Thanks

 

[Guest]

This will concatenate columns A & B and put the result in column C. It
assumes data strats in row 2.

If you are not familiar with VBA code/macros:

Press Alt+F11 which will display the VBE(Visual Basic Editor). In the
Project window, right click on the VBAProject (....) and then do
INSERT==>Module. Copy/paste the code below.
To run, place the cursor in the code and click the RUN Macro (Greren
arrowhead) button.

HTH

Sub ab()
Dim rnga As Range, rngb As Range
Dim cella As Range, celb As Range
Dim r As Long
With Worksheets("Sheet1") '<== change "Sheet1" to your sheet if required
r = 2
Set rnga = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
Set rngb = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
For Each cellb In rngb
For Each cella In rnga
Cells(r, "C") = cella & " " & cellb
r = r + 1
Next cella
Next cellb
End With
End Sub