# Excel Question



## pull (Dec 8, 2002)

Need help on getting the number of combinations within a group of cells.
If this can be done, will excel list and print the combinations?

A	B C
1	a 
2	b 
3	c 
4	d 
5	e 

Would like to know how to determine the number of conbinations are in Range A1 thre A6

Thanks in advance.
Pull


----------



## XL Guru (Aug 30, 2003)

The number of combinations for 6 "things" is 720. This is called a factorial, apparently (who knew?).

To list them, you can paste the below code, from John Walkenbach's site, into a plain VBA module, then run the GetString macro.

HTH,
Andy

Dim CurrentRow
Sub GetString()
Dim InString As String
msg = "Do You Want to Add a Sheet Y/N" & Chr(13) _
& "If No, Column A Will be Overwritten"

Ans = MsgBox(msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Sheets.Add
Case vbNo
GoTo carryon
Case vbCancel
Cancel = True
Exit Sub
End Select
carryon:
InString = InputBox("Enter text to permute:")
If Len(InString) < 2 Then Exit Sub
If Len(s) >= 8 Then
MsgBox "Too many permutations!"
Exit Sub
Else
ActiveSheet.Columns(1).Clear
CurrentRow = 1
Call GetPermutation("", InString)
End If
End Sub

Sub GetPermutation(x As String, y As String)
' The source of this algorithm is unknown
Dim i As Integer, j As Integer
j = Len
If j < 2 Then
Cells(CurrentRow, 1) = x & y
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub


----------



## pull (Dec 8, 2002)

Thanks XL Guru, I'll give this a try.


----------



## slurpee55 (Oct 20, 2004)

This is great as long as all of your items are unique or you want to know the total possible # of combinations. However, if you have the same item several times,
A
1
2
1
3
4
5
then your # of combinations is not = n! (that is n, the number, and ! is the factorial sign), but rather it is equal to (n-# of duplicates)! For instance,
A
1
2
3
having 4 unique items has n!=24 but
A
1
2
1
has 3 unique items and n!=6


----------



## XL Guru (Aug 30, 2003)

Yes, for a non-unique set of values you'd need to adjust "text to permute". Point taken, thanks Loche.


----------

