# How to combine all cells equal or close to a given value

#### Gabrielle_erre

##### New Member
Hey... I'm a newie on Excel and I've seen a lot of smart people around. any of you came across the given exemple below?

I've got a column of different products, and I'd like to find all combination of procucts up to a maximum value of 2,45 meters and count all those combinations to optimise the transport.I tried to sum all the numbers and divide them by 2,45 but I can not split the products. I also have some 0 values on the table that I do not need. Anyone has an idea about what I can use...?

 number of pallets used high 1 0,88 1 1,56 1 2,24 1 3,72 1 1,08 1 1,96 1 3,64 1 2,08 1 2,76 1 0,68 1 2,56 1 1,08 1 1,76 1 3,24 2 3,16 2 3,56 0 0 0 0 0 0 0 0

<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>
</tbody>

Is there a way through Excel (or anything else if you have in mind) that can list me the best number combinations ?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Re: How to ombine all cells equal or close to a given value

there are only two products and many are over 2.45. let alone combine, by themselves are too big for carrier. can you make your problem more clear?
Ravishankar

Re: How to ombine all cells equal or close to a given value

Thank you Ravishankar! The first example was not complete....and it was not very clear.
I would like to combine all the products from column A by they're hights in colomn D, in order to optimise the space. The maximum hight is 2,45 and I would like to know if there is any logical argument I can put, in otder to group them to a value closer to 2,46. for exceple C3 will have 1x2,45 + 1x0,03.
I would like to keep the 2,45 (1xfull) and 0,03 to combine with another one. I will need a function for all the column D. Is possible something like that in Excel...?

 A B C D 1 Product quantity No. Of pallet 2 p1 0,54 1 3 p2 4,48 2 4 p3 6,42 3 5 p4 5,42 3 6 p5 2,88 2 7 p6 0,94 1 8 p7 0 0 9 p8 1,06 1

<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>
</tbody>

Re: How to ombine all cells equal or close to a given value

could you show expected result based on example from post #3 ?

Last edited:
Re: How to ombine all cells equal or close to a given value

hey, I was thinking about something like:

 complete pal (2,45) rest to combine Combined pallets max 2,45 = Total to transport 0,54 p1 p2 0,3 p2 p2+p4+p5+p8 0,3 +0,43+0,55+1,06 p3 1,52 p3 p1+p3 0,54+1,52 p3 p6 0,94 p4 p4 0,55 p4 p5 0,43 p5 p6 0,94 p6 p7 p8 1,06 p8

<tbody>
</tbody>

Last edited:
Re: How to ombine all cells equal or close to a given value

is that what you want ?
or I misunderstood...

 0.01​ 0.00​ 0.01​ 0.00​ 0.00​ 0.01​ 0.01​ Product P1​ P2​ P3​ P4​ P5​ P6​ P7​ P8​ TTT​ 2.42​ 2.43​ 2.44​ 2.45​ 2.46​ 2.47​ 2.48​ Given value: 2.45 +/- 0.03 Qty 0.54​ 0.3​ 1.52​ 0.55​ 0.43​ 0.94​ 0​ 1.06​ 2.43​ 2.43​ 2.45​ 2.45​ 2.46​ 2.46​ 2.49​ 0​ 0​ 0​ 0​ 1​ 1​ 1​ 1​ 2.43​ 0​ 0​ 0​ 0​ 1​ 1​ 0​ 1​ 2.43​ 1​ 1​ 0​ 1​ 0​ 0​ 0​ 1​ 2.45​ 1​ 1​ 0​ 1​ 0​ 0​ 1​ 1​ 2.45​ 1​ 0​ 0​ 1​ 1​ 1​ 1​ 0​ 2.46​ 1​ 0​ 0​ 1​ 1​ 1​ 1​ 0​ 2.46​ 1​ 0​ 1​ 0​ 1​ 0​ 1​ 0​ 2.49​

Last edited:
Re: How to ombine all cells equal or close to a given value

YES!

Just that I can not insert the error... and I do not need exact hight of 2,45, just to optimise the hight of pallets. for ex. the rest of incomplete pallets : p1+p2+p4=2,40 is ok, but if it would be 2,46, than I mix any 2 products together and make 1 pallet, +1 additional pallet.

Last edited:
Re: How to ombine all cells equal or close to a given value

so you can take 2.43 and 2.45 then the rest optimise again for 2,45 or less
with Solver

as you can see there is no definite result

Last edited:
Re: How to ombine all cells equal or close to a given value

And how did you get the results ? what have you used?

Re: How to ombine all cells equal or close to a given value

I did it with Solver, I you don't see it on the Data tab go to Developer tab then Excel Add-ins and mark Solver there

Here is a example excel file

look at A1...G1 (formula) and A3...G3 (another formula)

Replies
16
Views
1K

1,196,054
Messages
6,013,124
Members
441,749
Latest member
batel19

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back