Optimize a database by two variables.

See more:

MS-Excel

database

I have a database of shapes, each shape has a color and value.

There are only 6 types of shapes and each shape can be used only once for a combination. Each shape in the database also has a different value and a different color of the shape. There are eight colors total, each color requires 2 or 4 of the shapes to be that color (ie. blue requires 2 blues and red requires 4 reds). I am trying to build an optimizer to analyze the data to show me the highest value for a combination of shapes that still follow the color requirements.

It's for a work issue actually, I'm an Army Criminal Intelligence analyst trying to use excel to perform this kind of rudimentary analysis.

My data:

Oval blue 21
Square brown 16
Circle Red 15
Diamond brown 13
Circle brown 13
Triangle white 13
Square green 12
Square Red 12
Diamond brown 12
Diamond green 12
Oval Red 12
Square Red 11
Rectangle Red 30
Rectangle Red 30
Rectangle Red 30
Rectangle Red 30
Rectangle Red 30
Rectangle brown 30
Rectangle green 30
Rectangle green 30
Rectangle yellow 30
Square Red 11
Rectangle Red 11
Oval Red 11
Square Red 11
Triangle Red 11
Square Red 10
Square Red 10
Diamond brown 10
Square brown 9
Circle green 9
Oval Red 9
Square Red 9
Diamond Red 8
Triangle Red 8
Circle green 8
Circle blue 8
Oval green 8
Oval Red 8
Oval Red 8
Square Red 7
Diamond brown 7
Diamond Red 7
Circle Red 7
Diamond blue 7
Square Red 7
Diamond Red 7
Circle brown 6
Circle Red 6
Square brown 5
Square brown 5
Square brown 5
Square brown 5
Diamond brown 5
Diamond brown 5
Diamond brown 5
Diamond blue 5
Diamond Red 5
Triangle brown 5
Triangle brown 5
Triangle brown 5
Triangle green 5
Triangle blue 5
Triangle Red 5
Circle brown 5
Circle brown 5
Circle green 5
Circle green 5
Circle Red 5
Oval brown 5
Oval brown 5
Oval Red 5
Square green 5
Square Red 5
Diamond brown 5
Circle brown 5
Circle brown 5
Circle green 5
Oval green 5
Square green 5
Oval brown 5
Square green 4
Square green 4
Diamond brown 4
Diamond blue 4
Diamond Red 4
Triangle brown 4
Circle brown 4
Circle brown 4
Circle Red 4
Circle Red 4
Oval brown 4
Oval brown 4
Diamond brown 4
Triangle brown 4
Square green 4
Square Red 4
Diamond brown 4
Diamond brown 4
Diamond green 4
Triangle green 4
Triangle blue 4
Circle brown 4
Oval Red 4
Square Red 4
Diamond green 4
Diamond Red 4
Circle Red 4
Circle Red 4
Diamond green 4
Oval Red 4
Diamond Red 4

What I have tried:

I have tried manually looking....