How to group Values together where each group is = or < a specific total

I believe I posted this once about a year ago, but I am still trying to find a simple way to work this out.

I am a director of the design/quotes department for a lighting manufacturer, and we are trying to develop a quick way to quote projects with a calculator, but one issue is grouping runs onto drivers. If we were to input 20 different run lengths (this is linear lighting), it would be simple enough to have the calculator just give a single driver/transformer per run. But many times multiple runs could fit onto a single driver.

Basically I am trying to have an excel calculator that will do this. In the attachment you will see an example of how I've made this work (using a LOT of columns and rows), where it lists the wattage of each run (up to a maximum of 80 watts), and then it begins grouping them one by one onto drivers up to a maximum of 80.

The problem with this method is there are so many formulas building on top of each other, and if I allow for multiple "types" (different products on the same job), then there will be many tables like this, and the file will become VERY slow and clunky. I'm hoping to find an easier way to do it, not using VBA code (only functions and formulas). It's really critical to figure this out, so if you have some ideas I would be so grateful. When you read this and/or look at the example file, please let me know if I need to clarify something, and I will do my best.

Much Appreciated,
- Colby

Hey Bernie!

Thanks. That gets me started in a much better way. Now, if you scroll to the right of the spreadsheet, you will see one additional chart where I take the totals that have been grouped, and see if any of the groups can be combined once more. Since the runs are grouped one-by-one in the first chart, I noticed that some of the groups were very small because the run immediately following was too large to be grouped with it, but at the same time that small run/group could potentially be combine with a group further down the list.

So the table on the far right helps combine those smaller ones. It is similar, but it's basically categorizing each group into three camps -- "A" means that group is less than 3/8 the capacity (80-watts) of the driver (0.375), "B" means it is between 3/8 and 5/8 the driver capacity (80-watts), and "C" means it is over 5/8 the capacity. And with that, it tries to add up each to a maximum of 1 (i.e. any groups of 0.375 can be combined with any others of 0.375 or 0.625, to clean up those small ones), and any leftovers of 1 or 0.625 remain by themselves on their own driver. This just gives an extra layer of grouping for efficiency.

Is there a way to simplify that portion as well, to get a total number of drivers at that point?

Much Appreciated,

- Colby

Hey Bernie,

Thank you for sharing that. Can you help me understand what's happening? In my bulky version, as you can see in the attached screenshot, based on the total for each initial group, it's grouping small ones with other small or medium sized ones (0.375 and 0.625 percentages respectively).

In your last version, it looks like you calculated the percentages for each group, but I don't see how it's grouping that with other small or medium sized groups (they seem to be ranked by their percentage in column F). So, for instance, the smallest group (Group 1) isn't being grouped with any other, from what I see. Am I wrong? That's the goal we're shooting for. I also see that group 5 and group 16 have the same "Rank", since they both have the same total watts. But both groups can't be grouped together, since they're too big.

So I guess I'm wondering if there's a way to see this mimic the way I have it in my larger, bulky version with a simpler method. Thanks again for being willing to help with this.

Much Appreciated,

- Colby

Is there a way to group them on the sheet? Meaning, there will be a physical grouping of them (either by some indicator like a "group" column with labels, such as "1", "2", etc.). The reason that would help is this feature will be integrated into a larger calculator, where it will automatically figure out how many drivers we will need, and display that on another sheet. The conditional formatting helps me visually see which runs/subgroups can be combined, but I don't see how the calculator could see that.

I really appreciate your help.
- Colby