Microsoft Excel 2003 (full product)

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

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 WtA1: 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.

You certainly made it easier by changing the requirement to "something just.lessthan 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

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

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

That is OUTSTANDING. I canâ€™t tell you how much I appreciate your help. I officially announce you're ALOT smarter than me. THANKS!!

You're welcome.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History