Help with Combination Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Santhosh Mani - 13 Dec 2005 06:16 GMT

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.

Reply to this Message

Bob Phillips - 13 Dec 2005 09:16 GMT

Do a Google group search on 'combinations permutations' with 'Larson' as the
author Myrna has posted many solutions to this.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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
[quoted text clipped - 12 lines]
> 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.

Reply to this Message

Bruno Campanini - 13 Dec 2005 14:24 GMT

>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
[quoted text clipped - 15 lines]
> 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

Reply to this Message

Santhosh Mani - 13 Dec 2005 17:49 GMT

Thank you very much for your reply and your solution is exactly what I am
looking for. But there is some problem in code I think. I made few changes in
the given code, SourceRange = [Sheet1!b3] and Set TargetRange = [Sheet1!c2].
Then in call macro function I replaced the figure 8 to 4.

In order to breifly explain the problem I reduced the number of items 1 - 4.
So when I run the macro the result is as follows. And you will notice that
combinations are duplicated and the fourth item,No.4 is not taken to make
combination. Could you please look into this problem. And one more thing
instead of seperating the combinations with space can I get the result by sum
of combinations, ie; instead of 1 2 3 I should get 1+2+3 = 6

Thanks a lot for your patience.

Santhosh > Kuwait

ColA    ColB
1    1
2    2
3    3
4    1
    2
    3
    1 2
    1 3
    2 3
    1 2
    1 3
    2 3
    1 2 3
    1 2 3
=================================================

> >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
[quoted text clipped - 89 lines]
> Ciao
> Bruno

Reply to this Message

Santhosh Mani - 14 Dec 2005 07:26 GMT

Dear Mr. Bruno, Could you please check my reply and solve my problem? thanks
and regards, santhosh

> Thank you very much for your reply and your solution is exactly what I am
> looking for. But there is some problem in code I think. I made few changes in
[quoted text clipped - 122 lines]
> > Ciao
> > Bruno

Reply to this Message