Concatenate two columns?

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

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

"uma" wrote:

>
> 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
>
>
> --
> uma
> ------------------------------------------------------------------------
> uma's Profile: http://www.excelforum.com/member.php...o&userid=33632
> View this thread: http://www.excelforum.com/showthread...hreadid=534045
>
>