Solve for Min Sum and Full Scope

[NZilger]

Hello,

I'm working on building an analysis tool and have hit a bit of a roadblock. I have a data set for a list of vendors in rows and type of scope across the columns. I also have a "Preliminary" vendor in the uppermost row of values which represents a budget. Both the count of vendors and scope are dynamic.

What I'd like to accomplish is to automatically find the lowest sum for full scope coverage. Scope cannot be split or doubled between vendors. If needed to complete scope then the budget number may be used, but that should be last resort. If a vendor is marked as Excluded then they should be omitted, if required then of course they should be required.

I've looked into the Solver Add-in and I'm stumped. I have some experience with VBA though this particular solution eludes me. If VBA I'm thinking a brute force method to iterate through all possible solutions would be necessary. (?)

What I'm in need of is some guidance. Could someone point me in the right direction?

Here's an example of the table. Please forgive me if the formatting is odd, the preview looks good. The last test post I attempted failed so I was unable to confirm.

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

BB_DIVISION 01​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]012100 Allowances[/TD]
[TD]013000 Administrative Requirements[/TD]
[TD]013000 Administrative Requirements[/TD]
[TD]015000 Temporary Facilities & Controls[/TD]
[TD]015000 Temporary Facilities & Controls[/TD]
[TD]015000 Temporary Facilities & Controls[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Exclude/Require[/TD]
[TD]Vendors Name[/TD]
[TD][/TD]
[TD]Other[/TD]
[TD]Labor[/TD]
[TD]Sub[/TD]
[TD]Labor[/TD]
[TD]Material[/TD]
[TD]Sub[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Preliminary[/TD]
[TD]Bid Total[/TD]
[TD]

9000​

[/TD]
[TD="bgcolor: #00B050"]

115​

[/TD]
[TD]

1757​

[/TD]
[TD="bgcolor: #00B050"]

4055​

[/TD]
[TD]

4908​

[/TD]
[TD]

1748​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Test1[/TD]
[TD]

4000​

[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Test2[/TD]
[TD]

3999​

[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Test3[/TD]
[TD]

5000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #00B050"]x[/TD]
[TD][/TD]
[TD="bgcolor: #00B050"]x[/TD]
[TD="bgcolor: #00B050"]x[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Test4[/TD]
[TD]

1​

[/TD]
[TD="bgcolor: #00B050"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Answer 1[/TD]
[TD]

14711​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Answer 2[/TD]
[TD]

10656​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Answer 3[/TD]
[TD="bgcolor: #00B050"]

9171​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]Answer 4[/TD]
[TD]

19656​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: BB_DIVISION 01[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!