Merging multiple columns in Excel 2003

[gandhikr]

Column A
------
acne
pimple
scar


Column B
-----
treatment
surgery
cure
medication


Column C
------
mumbai
delhi
chennai
ahmedabad
pune


I want to combine the data and generate all possible combinations such as
acne treatment mumbai
acne treatment delhi
acne treatment chennai
acne treatment ahmedabad
acne treatment pune etc..

pimple treatment mumbai
pimple treatment delhi
pimple treatment chennai
pimple treatment ahmedabad etc..

how do I do it in Excel 2003?
What is the formula I should use so that I do not have to do manually?

Thanks in advance for help

 

[pedie]

try this

i for col A
k for col B
j for col C

return result in col D

[FONT=courier new]Sub test_1()[/FONT]
[FONT=courier new]    Dim i As Long[/FONT]
[FONT=courier new]    Dim k As Long[/FONT]
[FONT=courier new]    Dim j As Long[/FONT]

     [FONT=courier new]Range("D1").Value = "Result"
[/FONT]
[FONT=courier new]    For i = 1 To 3[/FONT]
[FONT=courier new]        For k = 1 To 4[/FONT]
[FONT=courier new]            For j = 1 To 5[/FONT]
[FONT=courier new]                Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value & " " & Cells(k, 2).Value & " " & Cells(j, 3).Value[/FONT]
[FONT=courier new]            Next j[/FONT]
[FONT=courier new]        Next k[/FONT]
[FONT=courier new]    Next i[/FONT]
[FONT=courier new]End Sub[/FONT]
[FONT=courier new]
[/FONT]

 

[gandhikr]

try this

i for col A
k for col B
j for col C

return result in col D

[FONT=courier new]Sub test_1()[/FONT]
[FONT=courier new]    Dim i As Long[/FONT]
[FONT=courier new]    Dim k As Long[/FONT]
[FONT=courier new]    Dim j As Long[/FONT]

     [FONT=courier new]Range("D1").Value = "Result"
[/FONT]
[FONT=courier new]    For i = 1 To 3[/FONT]
[FONT=courier new]        For k = 1 To 4[/FONT]
[FONT=courier new]            For j = 1 To 5[/FONT]
[FONT=courier new]                Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value & " " & Cells(k, 2).Value & " " & Cells(j, 3).Value[/FONT]
[FONT=courier new]            Next j[/FONT]
[FONT=courier new]        Next k[/FONT]
[FONT=courier new]    Next i[/FONT]
[FONT=courier new]End Sub[/FONT]
[FONT=courier new]
[/FONT]

** Sorry to ask ** but I am newbi

Where do I put this code?

Thanks for your help..

 

[pedie]

from ur excel press

ALT + F11 (this will open up the VB Editor - white screen)
from the menu click on INSERT > CLICK ON MODULE

paste this code
click anywhere within the code and press F5 to test run the code

 

[gandhikr]

from ur excel press

ALT + F11 (this will open up the VB Editor - white screen)
from the menu click on INSERT > CLICK ON MODULE

paste this code
click anywhere within the code and press F5 to test run the code

Thank you again for your help.

However, I am getting partial results..

I have more data in column A, more data in column B and more in column C

Could you help me out with macro which checks & generates the data from 1st to last in the column and gets results

 

[pedie]

try this one.

[/FONT][FONT=courier new]Sub test_1()[/FONT]
[FONT=courier new]    Dim i As Long, ilr As Long[/FONT]
[FONT=courier new]    Dim k As Long, klr As Long[/FONT]
[FONT=courier new]    Dim j As Long, jlr As Long[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    ilr = Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=courier new]    klr = Range("B" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=courier new]    jlr = Range("C" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    For i = 1 To ilr[/FONT]
[FONT=courier new]        For k = 1 To klr[/FONT]
[FONT=courier new]            For j = 1 To jlr[/FONT]
[FONT=courier new]                Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value & " " & Cells(k, 2).Value & " " & Cells(j, 3).Value[/FONT]
[FONT=courier new]            Next j[/FONT]
[FONT=courier new]        Next k[/FONT]
[FONT=courier new]    Next i[/FONT]
[FONT=courier new]End Sub

 

[gandhikr]

try this one.

[FONT=courier new]Sub test_1()[/FONT]
[FONT=courier new]    Dim i As Long, ilr As Long[/FONT]
[FONT=courier new]    Dim k As Long, klr As Long[/FONT]
[FONT=courier new]    Dim j As Long, jlr As Long[/FONT]

[FONT=courier new]    ilr = Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=courier new]    klr = Range("B" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=courier new]    jlr = Range("C" & Rows.Count).End(xlUp).Row[/FONT]

[FONT=courier new]    For i = 1 To ilr[/FONT]
[FONT=courier new]        For k = 1 To klr[/FONT]
[FONT=courier new]            For j = 1 To jlr[/FONT]
[FONT=courier new]                Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value & " " & Cells(k, 2).Value & " " & Cells(j, 3).Value[/FONT]
[FONT=courier new]            Next j[/FONT]
[FONT=courier new]        Next k[/FONT]
[FONT=courier new]    Next i[/FONT]
[FONT=courier new]End Sub[/FONT]

It is solving my problem. thank you..