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

[Gabrielle_erre]

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...?
[TABLE="width: 160"]
<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl73, width: 80"]number of pallets used[/TD]
[TD="class: xl71, width: 80"] high[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"] 1[/TD]
[TD="class: xl70, align: right"]0,88[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]1,56[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]2,24[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]3,72[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]1,08[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]1,96[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]3,64[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]2,08[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]2,76[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]0,68[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]2,56[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]1,08[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl70, align: right"]1,76[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]1[/TD]
[TD="class: xl70, align: right"]3,24[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]2[/TD]
[TD="class: xl70, align: right"]3,16[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]2[/TD]
[TD="class: xl70, align: right"]3,56[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]0[/TD]
[TD="class: xl70, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl70, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]0[/TD]
[TD="class: xl70, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl70, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[ravishankar]

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

 

[Gabrielle_erre]

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...?

[TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl70, width: 80"]A[/TD]
[TD="class: xl70, width: 80"]B[/TD]
[TD="class: xl70, width: 80"]C[/TD]
[TD="class: xl70, width: 80"]D[/TD]
[/TR]
[TR]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]Product[/TD]
[TD="class: xl70"]quantity[/TD]
[TD="class: xl70"]No. Of pallet[/TD]
[/TR]
[TR]
[TD="class: xl70"]2[/TD]
[TD="class: xl70"]p1[/TD]
[TD="class: xl69, align: right"]0,54[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl70"]3[/TD]
[TD="class: xl70"]p2[/TD]
[TD="class: xl69, align: right"]4,48[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl70"]4[/TD]
[TD="class: xl70"]p3[/TD]
[TD="class: xl69, align: right"]6,42[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl70"]5[/TD]
[TD="class: xl70"]p4[/TD]
[TD="class: xl69, align: right"]5,42[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl70"]6[/TD]
[TD="class: xl70"]p5[/TD]
[TD="class: xl69, align: right"]2,88[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl70"]7[/TD]
[TD="class: xl70"]p6[/TD]
[TD="class: xl69, align: right"]0,94[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl70"]8[/TD]
[TD="class: xl70"]p7[/TD]
[TD="class: xl69, align: right"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD="class: xl70"]9[/TD]
[TD="class: xl70"]p8[/TD]
[TD="class: xl71"]1,06[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

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

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

 

[Gabrielle_erre]

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

hey, I was thinking about something like:

[TABLE="width: 704"]
<tbody>[TR]
[TD]complete pal (2,45)[/TD]
[TD]rest to combine[/TD]
[TD]Combined pallets max 2,45[/TD]
[TD]=[/TD]
[TD]Total to transport[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0,54 p1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p2[/TD]
[TD]0,3 p2[/TD]
[TD]p2+p4+p5+p8[/TD]
[TD]0,3 +0,43+0,55+1,06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p3[/TD]
[TD]1,52 p3[/TD]
[TD]p1+p3[/TD]
[TD]0,54+1,52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p3[/TD]
[TD][/TD]
[TD]p6[/TD]
[TD="align: right"]0,94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p4[/TD]
[TD]0,55 p4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p5[/TD]
[TD]0,43 p5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p6[/TD]
[TD]0,94 p6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p8[/TD]
[TD]1,06 p8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

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

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]

0.01​

[/td][td]

0.00​

[/td][td]

0.01​

[/td][td]

0.00​

[/td][td]

0.00​

[/td][td]

0.01​

[/td][td]

0.01​

[/td][td][/td][td]Product[/td][td]

P1​

[/td][td]

P2​

[/td][td]

P3​

[/td][td]

P4​

[/td][td]

P5​

[/td][td]

P6​

[/td][td]

P7​

[/td][td]

P8​

[/td][td][/td][td]

TTT​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2.42​

[/td][td]

2.43​

[/td][td]

2.44​

[/td][td]

2.45​

[/td][td]

2.46​

[/td][td]

2.47​

[/td][td]

2.48​

[/td][td]Given value: 2.45 +/- 0.03[/td][td]Qty[/td][td]

0.54​

[/td][td]

0.3​

[/td][td]

1.52​

[/td][td]

0.55​

[/td][td]

0.43​

[/td][td]

0.94​

[/td][td]

0​

[/td][td]

1.06​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2.43​

[/td][td]

2.43​

[/td][td]

2.45​

[/td][td]

2.45​

[/td][td]

2.46​

[/td][td]

2.46​

[/td][td]

2.49​

[/td][td][/td][td][/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td][/td][td]

2.43​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td][/td][td]

2.43​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td][/td][td]

2.45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td][/td][td]

2.45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td][/td][td]

2.46​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td][/td][td]

2.46​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td=bgcolor:#FFC7CE]

1​

[/td][td]

0​

[/td][td][/td][td=bgcolor:#FFFF00]

2.49​

[/td][/tr]
[/table]

 

[Gabrielle_erre]

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.

 

[sandy666]

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

 

[Gabrielle_erre]

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

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

 

[sandy666]

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)