Choosing top scoring row (with complex logic/rules)

[z3115]

I have a set of data with different groups ("colors"). For each group (color), there are questions that apply to "categories" ("A", "B", "C", etc.). Each has a numerical score. I need to pick the highest scoring question for each category, for each group (color).



Example: Group "Blue" has 2 questions that apply to category "A". One has a score of 4, the other has a score of 2. The question with a score of 4 is the one that should be used.



This is simple enough, but there is an additional rule that makes everything more complicated: Some questions apply to multiple categories, but these questions can only be used for 1 category (or not used at all).



Because of this rule, there are several scenarios that come up, and as a result certain logic that needs to be applied:

1. If a question applies to multiple categories and one of those categories has no other questions that apply to it, the question should be used for that category [Ex: if question 1 applies to categories A & B, and there are no other questions that apply to category A, question 1 should apply to category A. If neither category A nor category B have any other questions that apply to it, question 1 can be applied to either (but not both). It doesn't matter which one it's applied to.]
2. If a question applies to multiple categories and each of those categories have other questions that apply to that category, the question that applies to multiple categories should be applied to lowest scoring category [Ex: "Question 4" applies to categories B & C and has a score of 7; "question 6" only applies to category B and has a score of 3. "Question 11" only applies to category C and has a score of 5. Since "question 6" scores worse than "question 11", "question 4" should be applied to category B. Note that if "question 4" had a score of 3 or lower, it would not be applied to either category

Is there any sort of formula or VBA code that can do anything like this?



Thank you all!




Example (the last column is the one I'm trying to automate):

Color	Question	Categories it can be used for	Score	Category to apply it to
Red	blah blah	A	4	Don't Use
Red	xyz	A;B	5	A
Red	gibberish	C	4	C
Red	hello	C;D	5	D
Red	lol	B	6	B
Blue	ggdfklj	A	4	A
Blue	blah blah	B	6	B
Blue	xyz	C	4	C
Blue	gibberish	B;C	3	Don't Use
Blue	hello	D	1	D
Green	ssasas	A:B	6	A
Green	sdsdff	B	4	B
Green	fffrrd	C	4	Don't Use
Green	blah	D	6	D
Green	brrrr	C;D	8	C

<tbody>
</tbody>