Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mark4006
 
Posts: n/a
Default Generating excel combinations

I have a list of range of data like
A 20
B 3
C 4
D 26
E 10
F 3
G 7


I would like to be able to generate a table with all the combinations of 4
selections which total 40 or less. Any sugestions as to how I could do this
in excel.

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

To quote Myrna Larson earlier today

Go to Google newsgroup search and search for

combination equal number

in groups microsoft.public.excel.*

You'll find lots of hits on how to do this with Solver.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mark4006" wrote in message
...
I have a list of range of data like
A 20
B 3
C 4
D 26
E 10
F 3
G 7


I would like to be able to generate a table with all the combinations of 4
selections which total 40 or less. Any sugestions as to how I could do

this
in excel.



  #3   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

Excel's solver would give you only one solution, I think.

In Don Knuth's Pre-Fascicle 3A you will find a solution
(exhaustive but not ideal, as he mentions) which I put
into VBA (see below).

Copy the code into the VBA editor, run it and press CTRL G
to see the results.

HTH,
Bernd

--------- Snipp here for code -------------

Option Explicit

'Knuth fasc3a.pdf, page 11 of 65, Filling a rucksack

Sub Fill_Rucksack()

Const n As Long = 7
Dim w(0 To n) As Long
Dim delta(1 To n) As Long 'delta(j) = w(j) - w(j-1) for 1
<= j < n
Dim t As Long
Const NCap As Long = 40
Dim c(0 To n) As Long
Dim r As Long
Dim i As Long
Dim length As Long

w(0) = 0
w(1) = 3
w(2) = 3
w(3) = 4
w(4) = 7
w(5) = 10
w(6) = 20
w(7) = 26

F1: 'Initialize

t = 0
c(0) = n
r = NCap

F2: 'Visit

'Visit the combination c(0)...c(t), which uses NCap -
r units of capacity
length = 0
For i = 0 To t
length = length + w(c(i))
Next i
If length <= NCap And w(c(t)) < 0 Then
Debug.Print "Solution: ";
For i = 0 To t: Debug.Print "["; c(i); "]"; w(c
(i));: Next i
Debug.Print
End If

F3: 'Try to add w(0)

If c(t) 0 And r = w(0) Then
t = t + 1
c(t) = 0
r = r - w(0)
GoTo F2
End If

F4: 'Try to increase c(t)

If t = 0 Then
GoTo Terminate
End If
If c(t - 1) c(t) + 1 And r = delta(c(t) + 1) Then
c(t) = c(t) + 1
r = r - delta(c(t))
GoTo F2
End If

F5: 'Remove c(t)

r = r + w(c(t))
t = t - 1
GoTo F4

Terminate:

End Sub


---------- Snipp here for solutions ----------

Solution: [ 7 ] 26
Solution: [ 7 ] 26 [ 1 ] 3
Solution: [ 7 ] 26 [ 2 ] 3
Solution: [ 7 ] 26 [ 2 ] 3 [ 1 ] 3
Solution: [ 7 ] 26 [ 3 ] 4
Solution: [ 7 ] 26 [ 3 ] 4 [ 1 ] 3
Solution: [ 7 ] 26 [ 3 ] 4 [ 2 ] 3
Solution: [ 7 ] 26 [ 3 ] 4 [ 2 ] 3 [ 1 ] 3
Solution: [ 7 ] 26 [ 4 ] 7
Solution: [ 7 ] 26 [ 4 ] 7 [ 1 ] 3
Solution: [ 7 ] 26 [ 4 ] 7 [ 2 ] 3
Solution: [ 7 ] 26 [ 4 ] 7 [ 2 ] 3 [ 1 ] 3
Solution: [ 7 ] 26 [ 4 ] 7 [ 3 ] 4
Solution: [ 7 ] 26 [ 4 ] 7 [ 3 ] 4 [ 1 ] 3
Solution: [ 7 ] 26 [ 4 ] 7 [ 3 ] 4 [ 2 ] 3
Solution: [ 7 ] 26 [ 5 ] 10
Solution: [ 7 ] 26 [ 5 ] 10 [ 1 ] 3
Solution: [ 7 ] 26 [ 5 ] 10 [ 2 ] 3
Solution: [ 7 ] 26 [ 5 ] 10 [ 3 ] 4


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
I get a program error when I download an excel template Ladybug Excel Discussion (Misc queries) 3 March 4th 05 01:02 AM
html to excel nellie Excel Discussion (Misc queries) 4 February 8th 05 11:37 PM
opening multiple instances of excel John B Excel Discussion (Misc queries) 4 January 29th 05 12:31 AM
Generating Correlated Random Values in Excel Randy Excel Discussion (Misc queries) 2 January 16th 05 10:50 PM
Shortcut file fails to open JimH Excel Discussion (Misc queries) 3 January 15th 05 11:13 PM


All times are GMT +1. The time now is 12:42 PM.

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"