Assign Option to Vehicle using rank and other criteria

[zapotnym]

Hello,

This is my first post, so thank you for your help!!!

I have a problem where I have multiple vehicles that can be assigned only 1 of 8 options. Each option is ranked to show which option is preferred for the vehicle, ie. a rank of 1 is the best option. Further to this, there can be a maximum # of each option assigned among the entire pool of vehicles. ie, if all vehicles show option 7 with a rank of 1, not all can be assigned to this rank, and some will need to be assigned to their 2nd, 3rd....etc rank.

Attached is a simple excel sheet to show an example. I need to best fit the data so vehicles are assigned their best option, without exceeding the Max # allowed for each option.

Can anyone help with functions, macros, or vba to accommodate this?

Thanks again.