# 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 --- ColumnBWt Lbl --- Weight AmtA1: a --- B1: 0.125A2: b --- B2: 0.250A3: c --- B3: 0.500A4: d --- B4: 0.750A1: e --- B1: 1.000A2: f --- B2: 2.000A3: g --- B3: 2.000A4: h --- B4: 5.000Now I have a target weight I am tring to achieve, lets say it's 4.244 lbsI 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

#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 ---------- ColumnCWt Lbl ----- Weight Amt -------- Target WtA1: a ----- B1: 0.125 ---------- C1: 4.256A2: b ----- B2: 0.250A3: c ----- B3: 0.500A4: d ----- B4: 0.750A1: e ----- B1: 1.000A2: f ----- B2: 2.000A3: g ----- B3: 2.000A4: h ----- B4: 5.000Now I have a target weight I am trying to achieve, lets say it's 4.256 lbsI 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 •