|
Help with Combination Function
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. |
Help with Combination Function
Do a Google group search on 'combinations permutations' with 'Larson' as the
author Myrna has posted many solutions to this. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "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. |
Help with Combination Function
"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 |
Help with Combination Function
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 ================================================= "Bruno Campanini" wrote: "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 |
Help with Combination Function
Dear Mr. Bruno, Could you please check my reply and solve my problem? thanks
and regards, santhosh "Santhosh Mani" wrote: 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 ================================================= "Bruno Campanini" wrote: "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 |
| All times are GMT +1. The time now is 01:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com