What formula can be used in Excel to calculate this?

Question

excel

There are 7 kinds of goods in large quantities. And there are 6 cars that can only carry certain types of goods and a strictly defined number of pieces.
Example
Machine 1 :

• Coffee 1000
  
• Alcohol 2000
  
• Fruit 500
  

What formula can be used to calculate how many and what kind of cars are needed to transport all the goods, and those that did not "fit" were displayed below (optional).
Some vehicles carry the same type of goods but different quantities.
Can you tell me what is the formula for doing this? And is it possible at all? I'm not familiar with Excel, but I really want to implement it in it.

Answer 1

Suggest or do for you? Show your work, what worked and what didn't.
Otherwise - freelance sites;)

Answer 2

The key phrase is "but I really want to"
I think you will not find a single formula.
You can write your own calculation algorithm in Excel using the usual summation and division formulas.
If you need to do this once, then filtering, highlighting to another sheet will help,
If you need to do this often, you need Excel programming, then this is on order.

Answer 3

This is not so difficult to solve with the help of the "Search for a solution" add-on. There are quite a few examples like yours on the internet.