Creating a Formula that Divide Data to respective persons

[afqhumrh]

Hi. I'm not very familiar with Excel functions..

I've a data set here, where I need to divide the projects between the students.

However, the rewards received must be <= 100.

And Project No.AA4 must only be assign to John.

I've tried some ways but its not working. Makes me wonder whether I can build the formula based on this data and limitation. This data will be used in Power BI too.

Any idea or thoughts on which functions I can use? Please help. Thanks. 

[#2]

@afqhumrh 

Let me ask:

• Would you be so kind as to post the actual spreadsheet, not just an image. That would help people on this end help you. Otherwise you're in effect asking us to create the spreadsheet based on your image.
• Would you answer a few more questions about how these assignments are to be spread out.
  • Is each assignment (other than John's to project AA4) to be made randomly?
  • Is there a maximum number of students to be assigned to any given project?
  • Can John have a partner in AA4?
  • Are the assigned projects to stay the same for both quarters?
  • Is there anything else you should be telling those who might have some ideas, any other parameter that you're taking for granted but isn't really obvious to those of us on the outside?