Excel: Finding Combinations for Sum

Microsoft Excel 2003 (full product)
May 26, 2010 at 18:52:39
Specs: Windows XP
I'm not sure if this is possable in Excel but I'd sure love if I could get it to work.

I have a column of weights (all different sizes). Each weight has a weight lable column next to the weight amount column. For example:

ColumnA --- ColumnB
Wt Lbl --- Weight Amt
A1: a --- B1: 0.125
A2: b --- B2: 0.250
A3: c --- B3: 0.500
A4: d --- B4: 0.750
A1: e --- B1: 1.000
A2: f --- B2: 2.000
A3: g --- B3: 2.000
A4: h --- B4: 5.000


Now I have a target weight I am tring to achieve, lets say it's 4.244 lbs
I need to know which weights (identified by its label) will be required to just exceed the target weight in one cell. And the remainder in another cell.

In the example above the answer would be f,g,b (which adds up to 4.250 lbs) with remainder of .006 lbs


See More: Excel: Finding Combinations for Sum

Report •


#1
May 26, 2010 at 21:54:08
When I scribbled this question up I got a bit confused. Sorry about that. Let me try again.
_________________________

I'm not sure if this is possible in Excel but I'd sure love if I could get this to work.

I have a column of weights (different sizes). Each weight is labeled in the column next to the weight amount column. And a Target weight in another column. For example:

ColumnA ----- ColumnB ---------- ColumnC
Wt Lbl ----- Weight Amt -------- Target Wt

A1: a ----- B1: 0.125 ---------- C1: 4.256
A2: b ----- B2: 0.250
A3: c ----- B3: 0.500
A4: d ----- B4: 0.750
A1: e ----- B1: 1.000
A2: f ----- B2: 2.000
A3: g ----- B3: 2.000
A4: h ----- B4: 5.000


Now I have a target weight I am trying to achieve, lets say it's 4.256 lbs
I need to know which weights (identified by its label) will be required to equal something just less than the target weight (with the remainder identified in another cell).

In the example above the answer would be f,g,b (which adds up to 4.250 lbs) with a remainder (or rather shortage) of .006 lbs.



Report •

#2
May 27, 2010 at 07:50:05
You certainly made it easier by changing the requirement to "something just less than the target weight".

Try this code, which will put the combination in D1 and the remainder in E1:

Option Explicit
Sub FindCombo()
Dim myTarget, myTotal, myRemainder
Dim myWeight As Integer
Dim myCombo As String
'Grab Target Weight from C1
  myTarget = Cells(1, 3)
'Loop backwards through weights in B1:B8
   For myWeight = 8 To 1 Step -1
'If weight in cell is bigger than the target
'weight value, don't use it
    If Cells(myWeight, 2) > myTarget Then GoTo tooMuch
'If weight in cell is less than than the target weight value,
'value, grab the associated label and concatenate it with
'the other labels
      myCombo = myCombo & " " & Cells(myWeight, 1)
'Reduce target value by weight just used (set new target value)
      myTarget = myTarget - Cells(myWeight, 2)
'Keep track of weights used
      myTotal = myTotal + Cells(myWeight, 2)
tooMuch:
'Loop and find next usable weight
   Next
'Strip leading space from combination and put it in D1
    Cells(1, 4) = Right(myCombo, Len(myCombo) - 1)
'Calculate remainder and put it in E1
     myRemainder = Cells(1, 3) - myTotal
         Cells(1, 5) = myRemainder
End Sub


Report •

#3
May 27, 2010 at 13:31:24
DerbyDad03,

Thank you very much for the answer. I sure need this to work. I'm not sure how to use the answer though. Can you walk me though it?
If it'll be any easier using email, you can send an email to threehundredwby at hotmail dot com.
Thanks again for the help.
Bill


Report •

Related Solutions

#4
May 27, 2010 at 19:18:10
Right click the sheet where you have your table.

Select View Code.

Copy and Paste this code into the window that opens.

I've modified it so that it runs as soon as you make a change to C1.

Enter a number in C1

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTarget, myTotal, myRemainder
Dim myWeight As Integer
Dim myCombo As String
'Was change made to C1?
 If Target.Address = "$C$1" Then
'Is there a number in C1
  If Not WorksheetFunction.IsNumber(Cells(1, 3)) Then
   MsgBox "C1 must contain a number"
   Exit Sub
  End If
'Grab Target Weight from C1
  myTarget = Cells(1, 3)
'Loop backwards through weights in B1:B8
   For myWeight = 8 To 1 Step -1
'If weight in cell is bigger than the target
'weight value, don't use it
    If Cells(myWeight, 2) > myTarget Then GoTo tooMuch
'If weight in cell is less than than the target weight value,
'value, grab the associated label and concatenate it with
'the other labels
      myCombo = myCombo & " " & Cells(myWeight, 1)
'Reduce target value by weight just used (set new target value)
      myTarget = myTarget - Cells(myWeight, 2)
'Keep track of weights used
      myTotal = myTotal + Cells(myWeight, 2)
tooMuch:
'Loop and find next usable weight
   Next
'Strip leading space from combination and put it in D1
    Cells(1, 4) = Right(myCombo, Len(myCombo) - 1)
'Calculate remainder and put it in E1
     myRemainder = Cells(1, 3) - myTotal
         Cells(1, 5) = myRemainder
End If
End Sub


Report •

#5
May 27, 2010 at 23:49:03
That is OUTSTANDING. I can’t tell you how much I appreciate your help. I officially announce you're ALOT smarter than me.

THANKS!!


Report •

#6
May 28, 2010 at 03:04:27
You're welcome.

Report •

Ask Question