Generate data from 3 different columns

gandhikr

New member
Joined
Dec 13, 2012
Messages
2
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Personally, I'd use a macro for this. Assuming that your data table is in columns A, B and C of Sheet1, starting in Row 2, this should work:

Code:
Sub BuildList()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim lCount As Long
    
    Set wsSource = Worksheets("Sheet1")
    Set wsTarget = Worksheets("Sheet2")
    
    With wsSource
        For x = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
            For y = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
                For z = 2 To .Range("C" & .Rows.Count).End(xlUp).Row
                    wsTarget.Range("A1").Offset(lCount, 0).Value = _
                        .Cells(x, 1).Value & " " & .Cells(y, 2).Value & " " & .Cells(z, 3).Value
                    lCount = lCount + 1
                Next z
            Next y
        Next x
    End With
End Sub
 

gandhikr

New member
Joined
Dec 13, 2012
Messages
2
Reaction score
0
Points
0
Hello Ken,

Your Macro has saved a lot of time for me.. Thanks a lot..

I have another situation..

Sometimes I have data in column D & sometimes in column D + column E..

How do I get the results of these combinations?

Thanks in advance
 
Top