"Santhosh Mani" wrote in message
...
I want to list down all possible combinations of sizes from a size list. We
produce Paper Rolls in a specified size ie; 161 inch. We receive cusomer
orders in small sizes. So I want to know which sizes could be combined to
slice the Big roll to avoid maximum loss. For eg I have orders for 8
sizes,
say 31, 35, 39, 41, 45, 55, 57, 59 inches. If I could calculate the
combination of these 8 sizes with 1 to 8 items (with the formula -
=COMBIN(8,1)+=COMBIN(8,2)+=COMBIN(8,3)+ =COMBIN(8,4) so on) I am getting a
result of total 255 combinations. How can I get all these 255 combinations
list down in different row? If I get that result, I can filter the
combinations which match beween 159 and 161 inches.

I want a flexible solution where number of sizes could be varied all the
time, here in this example 8 sizes with combination of 1 to 8 items, so
the
formula should be flexible to calculate n sizes with combination of 1 to n
items. I hope my question is clear and it will be very helpful if one
could
help me with this. Thanks in advance for your valued solution.

This writes in TargetRange-down all the combinations
of n elements (2^n-1) located in SourceRange-down
==========================
Public Sub CombinazioniSS(ByVal NumElementi As Long, ByVal Classe As Long)
Dim i As Long, j As Long, k As Long, FactClasse As Long
Dim CS() As Long, NumComb As Long, SourceRange As Range
Dim TargetRange As Range, SingleComb As String

Set SourceRange = [Sheet10!CM25]
Set TargetRange = [Sheet10!CN25]

' NumComb = Numero delle combinazioni
' ------------------------------
NumComb = 1
For i = NumElementi To NumElementi - Classe + 1 Step -1
NumComb = NumComb * i
Next
FactClasse = 1
For i = Classe To 2 Step -1
FactClasse = FactClasse * i
Next
NumComb = NumComb / FactClasse
' ------------------------------
ReDim CS(1 To NumComb, 1 To Classe)
For i = 1 To Classe
CS(1, i) = i
Next
For i = 2 To NumComb
k = Classe
Do Until CS(i - 1, k) < NumElementi - Classe + k
k = k - 1
Loop
For j = 1 To k - 1
CS(i, j) = CS(i - 1, j)
Next
CS(i, k) = CS(i - 1, k) + 1
For j = k + 1 To Classe
CS(i, j) = CS(i, j - 1) + 1
Next
Next

' Stampa in TargetRange-down
For i = 1 To UBound(CS, 1)
SingleComb = ""
For j = 1 To UBound(CS, 2)
SingleComb = SingleComb & SourceRange(CS(i, j) - 1) & " "
Next
If IsEmpty(TargetRange) Then
TargetRange = SingleComb
ElseIf IsEmpty(TargetRange.Offset(1)) Then
TargetRange.Offset(1) = SingleComb
Else
TargetRange.Resize.End(xlDown).Offset(1) = SingleComb
End If
Next

End Sub
=======================
In order to have all C8,x (2^8-1) combinations,
simply call it with:
-----------------------------
Public Sub CombinazioniSempliciS()
Dim i As Integer

For i = 1 To 8
CombinazioniSS 8, i
Next

End Sub
---------------------------

Ciao
Bruno