Macro to make all possible combinations of data in various columns in excel sheet

Question

I am a complete novice at macros and have been given a task which if done manually, can take a lot of time. I have a worksheet which has data as below:

A                      B           C
abc,def,ghi,jkl      1,2,3     a1,e3,h5,j8

I would like this to be in the following format.

abc  1  a1
abc  2  a1
abc  3  a1
abc  1  e3
abc  2  e3
abc  3  h5

and so on to make all possible combinations. Any help will be great. Thanks

are you sure? I would try and get the input formatted better, certainly more , separation. — ColWhi, Apr 20 '11 at 12:13
how do you mean all possible combinations, at the moment, you are into the millions. — ColWhi, Apr 20 '11 at 12:16
@Sasquiha - I don't count millions from the OP's posted datasets, just 48... but if he wants to work with larger datasets, then it can easily grow — Mark Baker, Apr 20 '11 at 13:03

score 2 · Answer 1 · 2011-04-20T16:02:03.130

2

Alternative method

Private Sub Combinations()
Dim arrA() As String, arrB() As String, arrC() As String
Dim lngA As Long, lngB As Long, lngC As Long

With Sheet1  '(CHANGE SHEET IF REQUIRED)
   arrA = Split(.Range("A1"), ",")
   arrB = Split(.Range("B1"), ",")
   arrC = Split(.Range("C1"), ",")

   For lngA = LBound(arrA) To UBound(arrA)
     For lngB = LBound(arrB) To UBound(arrB)
        For lngC = LBound(arrC) To UBound(arrC)

        .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = arrA(lngA) & " " & arrB(lngB) &       " " & arrC(lngC)
        Next lngC
      Next lngB
    Next lngA
End With
End Sub

edited Apr 20 '11 at 16:02

answered Apr 20 '11 at 14:31

Ah, I had interpreted the original data as being in columns instead of being comma separated. This works. – Excellll Apr 20 '11 at 15:21
Sorry about the formatting. I have quite a big dataset.I tried this macro and its not working – UJ9 Apr 20 '11 at 15:42
Actually its 3 columns A,B & C. And there are some cells like A1,B1 & C1 as above has comma separated data in them. – UJ9 Apr 20 '11 at 15:44
@UJ9 - did you mean my code isn't working? My code assumes data is in sheet with codename 'Sheet1', you may need to change it. – Apr 20 '11 at 16:19
@UJ9, you should update your question to include that bit of information about how the data is formatted. – Excellll Apr 20 '11 at 17:46

Excellll · Answer 2 · 2011-04-20T15:34:05.240

This code will take the data from columns A, B, and C, and give the output you described in columns E, F, and G.

Sub combinations()

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim out() As Variant
Dim j, k, l, m As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range


Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("B1", Range("B1").End(xlDown))
Set col3 = Range("C1", Range("C1").End(xlDown))

c1 = col1
c2 = col2
c3 = col3

Set out1 = Range("E2", Range("G2").Offset(UBound(c1) * UBound(c2) * UBound(c3)))
out = out1

j = 1
k = 1
l = 1
m = 1


Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            out(m, 1) = c1(j, 1)
            out(m, 2) = c2(k, 1)
            out(m, 3) = c3(l, 1)
            m = m + 1
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop


out1.Value = out
End Sub

If on the other hand, your data is comma-separated in cells A1, B1, and C1, the following code will work similarly.

Sub combinations()

Dim c1() As String
Dim c2() As String
Dim c3() As String
Dim out() As Variant
Dim j, k, l, m As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range


Set col1 = Range("A1")
Set col2 = Range("B1")
Set col3 = Range("C1")

c1 = Split(col1.Value, ",")
c2 = Split(col2.Value, ",")
c3 = Split(col3.Value, ",")

Set out1 = Range("E1", Range("G1").Offset((UBound(c1) + 1) * (UBound(c2) + 1) * (UBound(c3) + 1)))
out = out1

j = 0
k = 0
l = 0
m = 1


Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            out(m, 1) = c1(j)
            out(m, 2) = c2(k)
            out(m, 3) = c3(l)
            m = m + 1
            l = l + 1
        Loop
        l = 0
        k = k + 1
    Loop
    k = 0
    j = j + 1
Loop
out1.Value = out
End Sub

Please ignore my formatting as its my first post. Excellll, I have tried your macro and it gives a run time error 6, overflow. Any suggestions? — UJ9, Apr 20 '11 at 15:39
Which macro did you use? And could you tell me at what line the error occurs? — Excellll, Apr 20 '11 at 17:38

Macro to make all possible combinations of data in various columns in excel sheet

2 Answers2

Linked