Count added pairs

[zzz111]

Hello!

Wondering if there is a way to get Excel to count up number of items that add up to 48 in a most efficient way. No macros/vba please. See attached for more details.
Untitled1.png

[Kaper]

You could use Solver (a built in add-in to excel to achieve this). There are many solver tuitorials - including microsoft site. If that is not enough, please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[AliGW]

Welcome to the forum.

Unfortunately, 'latest' is no help to us in terms of your Excel version, as there are desktop and subscription models. Please clarify in your profile - is it Excel 2019 or Office 365? And yes, it does and will make a difference, so please get it updated for us. Thanks.

[zzz111]

Thanks guys!

Yeah solver is definitely a no-go. It would have to be purely formula driven - with no user interaction..?
Attached is the file. Perhaps not possible?

[Kaper]

OK, I think in such case - always either one or two cuts pert panel, and no need for the optimization of the remaining part, it can be done with formulas.

See attached file - there are 2 helper columns (as your input data have empty cells inside) sorted ascending and descending with formulas (the second uses SMALL instead of LARGE) like:

Please Login or Register  to view this content.

then formula for first cut is very short - just take the next large cut (until all cuts were done):

Please Login or Register  to view this content.

while with second cut, we not only have to check if we still have cuts to do, but also check if together with longer cut it will fit the panel:

Please Login or Register  to view this content.

the key part here is (used twice) calculation of which small piece we have available (not yet cut) by counting all above listed cut pieces):

Please Login or Register  to view this content.

Legend:
green cells are simple formulas to count input cuts and used panels (every used panel have at least first cut) yellow cells
Yellow cells contain above formulas copied down (to gray ones).

Your "perhaps not possible" ws quite motivating :-) And I spend quite some time trying to produce formulas which would mimmic VBA approach - just a table or collection where you select biggest one and find the biggest out of remaining, which would fit.
Complicated and hardly legible.
But then I realized, that because we have no chance to make 3 or more cuts of one panel, and have no limits for optimization of remains, just want to minimize the number of used panels, we can always use (try to) the smallest one not yet cut.

So here it is :-)

[zzz111]

Kaper - it will take me time to digest all of this... But THANK YOU!

Let me spend some time on it tomorrow or day after and i will let you know!

[zzz111]

OK - Kaper... I am afraid I have wasted your time. If you are ever in Vancouver, lunch is on me!!!
I actually have an additional column that complicates things way too much - the height column. I am pretty sure Excel cannot handle this with formulas.
Suppose you need an addon that is 30" wide and 150" tall.
You can get this from a full 48" panel that gives you a leftover that is 18" wide x 150" tall. This means that this leftover can create 3 more addons of 18" wide that 50" tall... or 2 x 18"x75"tall...
Is there a way to average this somehow?
Not sure if any of this makes sense to you.

[zzz111]

The end goal is to figure out the total area of the actual panels together with panels required to form the addons.