Bill of Materials purchasing optimization (using Octopart APIs)

Like many people, I periodically need to purchase components for a somewhat lengthy bill of materials for board production. Figuring out where to buy what without mistakes can be a somewhat stressful task. Octopart is a very useful search engine and data source for electronic components purchasing. They provide APIs which can call vendor and quantity specific price, inventory, and minimum order quantity. Almost always more than one vendor is needed to overcome shortages in inventory and fulfill the entire order. The more vendors you use, the lower the potential total purchasing cost for the project. However there are diminishing returns as the number of vendors increases. At some point its not worth dealing with the extra paperwork so identifying the ideal combination and number of vendors is of great interest. I've previously written a spreadsheet to manually select vendors based on on case by case decision making given stock levels and prices. The problem with that approach is that restricting the number of vendors as you go is problematic. Based on this need I'd like an excel spreadsheet that calls Octopart APIs to optimally populate vendor choice for each part based on availability and lowest cost for a given number of vendors. This will allow me to increment the vendor quantity to get a feel for the most desirable course of action. For example: input: max 3 vendors allowed (of variable ~10+ options) output: 1: vendor list for minimum cost 2: vendor assignment for each part The challenge is that the minimum cost combination of those 3 vendors must be found. In the example above there are 120 combinations of 3 vendors given a choice of ten. (combinations) Each part has a different number of vendors that stock it and vendors are often out of stock. Summary: Develop an algorithm that handles the intent of these requirement including relevant exceptions, error handling and logic. Programming approaches other than excel are welcome if you believe they are more suited or you are more comfortable with them. The results should be formatted for easy output review and exportable as a CSV for purchasing. Please mention alternate approach if known ahead of time in your quote. Looking forward to working with you. Thanks! Best regards, Jim