Making Combinations in Excel:

[akrams]

Making Combinations in Excel:

Can anyone please help me a formula which can give me all possible combinations to the following:


A1 = OM-8
A2 = H-170
A3 = G-3
A4 = KIKUYU


I want all possible combinations like:

OM-8 & H-170
OM-8 & G-3
OM-8 & KiKuyu
OM-8 & H-170 & G-3
OM-8 & H-170 & G-3 & KiKuyu

 

[MickG]

Hi, You seem to be after Combinations and Sub Combinations (Not permutations)
If that's what you're after I have some code, but not a formula.
Let me know !!
Regards
Mick

 

[Oaktree]

Have a look at http://www.mrexcel.com/forum/showthread.php?t=63064 for some ideas

 

[akrams]

Hi, You seem to be after Combinations and Sub Combinations (Not permutations)
If that's what you're after I have some code, but not a formula.
Let me know !!
Regards
Mick

Code will do. Anything that could help me get me all possible combination. Thanks for all your help.

 

[MickG]

Hi, Place your Data in individual cells in Row (1).
Run the code, an "Input Box" appears, SELECT the range of cells in Row (1).
The Max is 15, which will give you approx 32,000 Rows of Sub Combinations and Combinations.
Combinations start in "A2".

Dim ray(), n, com, comgon, cl As Integer, Vu As Range, oRes
Dim oSt, oNu, oPc, iPc, obit, allnu, c, oVal(), res, oPst, cc
Dim L, oWd As Integer
cl = 1
cc = 1
On Error Resume Next
 Set Vu = Application.InputBox( _
    Prompt:="Please Select Range with String ", _
    Title:="String Combinations", Type:=8)

If Vu Is Nothing Then
    MsgBox "No Value Entered"
    Exit Sub
End If
L = Vu.Columns.Count

If L > 15 Then
    MsgBox "String too Large to Process"
    Exit Sub
End If

Range(Cells(2, 1), Cells(50000, L)).ClearContents
 oRes = Vu.Value
   oNu = Vu.Columns.Count


For n = 1 To oNu
   ReDim Preserve ray(n)
     ray(n) = n
       com = com & n & ","
 Next n

comgon = Left(com, Len(com) - 1)

For oPc = 1 To UBound(ray) - 1
      obit = ray(oPc)
  For iPc = oPc + 1 To UBound(ray)
     allnu = obit & "," & ray(iPc)
        c = c + 1
            cc = cc + 1
              ReDim Preserve oVal(c)
                oVal(c) = allnu
       Next iPc
Next oPc


Do Until allnu = comgon
   res = res + 1
    obit = oVal(res)
       oSt = Split(obit, ",")(UBound(Split(obit, ",")))

For iPc = oSt + 1 To UBound(ray)
     allnu = obit & "," & ray(iPc)
        c = c + 1
          cc = cc + 1
If c = 1000000 Then MsgBox "Limit Reached  " & c: Exit Sub
If cc = 50000 Then cc = 1: cl = cl + 1

 ReDim Preserve oVal(c)
     oVal(c) = allnu
       Next iPc
Loop
Dim oCols, oSp, oComb, oAc As Integer, oDn As Integer, Ac

ReDim oComb(1 To c, 1 To oNu)
For oDn = 1 To UBound(oComb, 1)

oSp = Split(oVal(oDn), ",")
 For Ac = 0 To UBound(oSp)
    Dim Tt
      For oWd = 1 To UBound(oRes, 2)
      
      If oWd = Val(oSp(Ac)) Then
       oComb(oDn, Ac + 1) = oRes(1, oWd)
End If
Next oWd
Next Ac
Next oDn

Range("A2").Resize(c, oNu).Value = oComb
 MsgBox "The Total Number of Combinations is " & c

Hope this is what you want.
Regards Mick