# Making Combinations in Excel:

#### akrams

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### MickG

##### MrExcel MVP
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

#### akrams

##### New Member
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

##### MrExcel MVP
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".
Code:
``````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

Replies
3
Views
235
Replies
3
Views
58
Replies
2
Views
55
Replies
5
Views
126
Replies
4
Views
78

1,191,706
Messages
5,988,187
Members
440,136
Latest member
dandanfielding

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back