Count and Maybe a concatenate?

Okay, I have a quandary. Each of the people on this sheet can get one of six awards:
Be Financially Responsible
Do the Right Thing
Embrace an Entrepreneurial Spirit
TEAMWORK
Value Our Customers
Value Our Employees

They can get 1 award, all 6, a combination, and even the same aware multiple times.

I don't know if this is possible, but I need to figure out a formula that figures out if they have 3 different awards and then labels it 'screaming eagle FY 23', if they have more than 3 awards then for every 3 different awards I need to have the formula indicate it by adding a -1 or a -2 or -3 to the 'screaming eagle' award. So, a person could have the following:

Screaming Eagle Fall FY23 -1
Screaming Eagle Fall FY23 -2
Screaming Eagle Fall FY23 -3

I've attached a spreadsheet; the formula would be in column C - the awards are in F2:F7.

I have a duplicate of the rows with what the data should say, starting on row 32

Is this possible?

Sorry if I was unclear. There's probably an easier way to explain it and I'm just having difficulty.

Tammy has gotten 9 different awards. Many of the awards are exactly the same. In order to get a 'screaming eagle' award you need three different awards. Additionally, you can get more than one screaming eagle, which are collections of three different awards. So she's got 2 screaming eagles (screaming eagle 1 and screaming eagle 2).

She could have 3, however she got two Value our Customer awards and a Value our employees award. Had she gotten one value our customer award, one value our employees award and a different award, that would qualify for a 3rd screaming eagle award.

Does that make sense?

Thank you for helping me with this. It seems close. I have upgraded to 365.

So, I'll try to define things.

EE's get awards, they can be one of six different categories.
Be Financially Responsible
Do the Right Thing
Embrace an Entrepreneurial Spirit
TEAMWORK
Value Our Customers
Value Our Employees


To get a 'screaming eagle' you have to have 3 awards comprised of different categories.

So, a legitimate award would be:
Do the Right Thing
Teamwork
Value our customers

Those are 3 awards, different categories.

An illegitimate award would be:

Value our customers
Value our customers
Team work
(This is invalid because two of the awards are the same, they have to be all different).

Or

Do the Right Thing
Do the Right Thing
Do the right Thing
(This is invalid because it's 3 awards, but they're all the same)

Or

teamwork
Embrace an entrepreneurial spirit.
(this is because it's not even 3 awards)


Tammy Bobby has 9 total awards. However, she only has enough for 2 screaming eagles because you cannot create 3 combos of 3 different awards.
Her awards:
Be Financially Responsible
Do the Right Thing
Do the Right Thing
Do the Right Thing
Value Our Customers
Value Our Customers
Value Our Customers
Value Our Customers
Value Our Employees

Broken down, it should look like this:
Be Financially Responsible - screaming eagle 1
Do the Right Thing - screaming eagle 1
Do the Right Thing - screaming eagle 2
Do the Right Thing
Value Our Customers - screaming eagle 1
Value Our Customers - screaming eagle 2
Value Our Customers
Value Our Customers (if this award were any other award, you could combine the 3 bolded for a screaming eagle 3, but since it's the same, it's invalid).
Value Our Employees - screaming eagle 2



I'm sorry that it's so complicated, I'm trying to make it make sense. Hopefully, this explains it a bit better.

Done and thank you!

That's interesting JeteMc and I think I'll keep that in mind for later. I don't think it's quite what I'm looking for at the moment, but thank you. I was looking for something I could plop into column C, that would determine the screaming eagle for the ee.