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

BB_DIVISION 01​			012100 Allowances	013000 Administrative Requirements	013000 Administrative Requirements	015000 Temporary Facilities & Controls	015000 Temporary Facilities & Controls	015000 Temporary Facilities & Controls
Exclude/Require	Vendors Name		Other	Labor	Sub	Labor	Material	Sub
	Preliminary	Bid Total	9000​	115​	1757​	4055​	4908​	1748​
	Test1	4000​	x	x	x
	Test2	3999​		x	x	x
	Test3	5000​			x		x	x
	Test4	1​	x
	Answer 1	14711​
	Answer 2	10656​
	Answer 3	9171​
	Answer 4	19656​

<tbody>
</tbody>

Sheet: BB_DIVISION 01

<tbody>
</tbody>

Thanks!