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

G

Gabrielle_erre

New Member
Joined
Jan 3, 2019
Messages
21
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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
 
Upvote 0
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]
 
Upvote 0
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:
Upvote 0
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]
 
Last edited:
Upvote 0
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]
 
Last edited:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Automatically get the x and y values to calculate a SLOPE
2
Replies
16
Views
2K
DILIPandey
DILIPandey

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top