To SUBTOTAL or not to SUBTOTAL?

[babsc01]

Hello...been trying to figure this one out lately. How can I identify unique members of a range by the contents of another range? Look at the attached example.

Suppose I want to identify those players that play in a combination of certain sports, and label them based on those combinations. Such as:

IF SPORT = "Baseball" + "Football" then PLAYER = "Major League"
IF SPORT = "Lacrosse" then PLAYER = "Net"
IF SPORT = "Football" and NOT "Lacrosse" then PLAYER = "Strong"

Something along those lines...I know it's a very crude example.

I could accomplish this with a Pivot Table, but I'm trying to write some code and/or formulas that will (1) uniquely identify each PLAYER, and (B) label the player based on a combination of sports.

Any ideas? THANKS!!!

[Zack Barresse]

Hi babsc,

I don't even know why I'm posting this, it's not an exact solution to your problem, but I guess it's an option. I was fiddling around and don't know if you can use this or not, but if you can, kudo. If not, you aren't going to hurt my feelings by deleting it.

Anyway, maybe you could use something like that.

[babsc01]

Zack...I didn't see anything in the attachment except the data I posted. Am I missing something? Thanks.

[Zack Barresse]

Oh gosh, sorry. There are 2 routines in that file. Ensure you enable macros. Hit Alt + F8, click babscUniqueness, and press Run. To reset, Press Alt + F8 and run clear.

[babsc01]

I get a bunch of #NAME? errors when I run the macro.

[Zack Barresse]

Holy moley, I'm off today. I mentioned it in the code, but not in a post. That makes use of the morefunc.xll add-in by Laurent Longre found here. You could always substitute some kind of Advanced Filter for Unique Values if you'd like. I just took the route I knew.

[babsc01]

TOO COOL, Zack. Looks like I'm gonna try that direction. The rest of it is just using COUNTIF to label the players. THANKS!!!!

[Zack Barresse]

Your welcome. Sorry about the hassle, and sorry it's not exactly what you were after, but I hope you can make something of it. The code could be cleaned up too. But I'm glad you like it. (Btw, should I mark this solved?)

[babsc01]

Yeah, this one can be solved...you took me in the right direction. Thanks.