Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Santhosh Mani
 
Posts: n/a
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruno Campanini
 
Posts: n/a
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Santhosh Mani
 
Posts: n/a
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Santhosh Mani
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 08:07 AM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"