This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
This hasn't been touched in a few days and it looks interesting so I had a crack at it in front of the tv tonight...
The first thing to look at here would be the optimization tool from the Prescriptive Toolbox, however that would require a rework of the question as one of your conditions is a sum of multiple rows. This may be possible with a re-structure of the data but I don't know that tool well enough to restructure the data quickly. There is an example that might lead you down the right path under 'Help > Prescriptive > 1_Optimization_Model_Input_Modes' (Optimization with Matrix Mode).
If you just want to solve your problem as in the excel sheet then we can construct every possible combination of ID's as it's not much data.
In the attached:
The Orange Box constructs the data
The Green Box runs every combination (This involves a poorly written iterative macro, I'm just not going to spend more time right now tidying it up, probably a silly mistake producing the extra 30k rows)
The Blue Box gets that back to a format we can summarize easily
The Purple Box finds the answer we're after
Parts of this could be re-used for similar problems, but I'm not sure how it could be generalised as I don't know all the things people 'solve' using Excel Solver. I'm sure there is probably better ways to construct and transform the data, but I tried to leave it exposed so it can be followed.
On the plus side, this solution also includes the conditions with bounds not met in Excel Solver.