PDA

View Full Version : Solved: To SUBTOTAL or not to SUBTOTAL?



babsc01
07-28-2004, 08:48 AM
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
07-28-2004, 10:34 AM
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. :D

Anyway, maybe you could use something like that. :dunno

babsc01
07-28-2004, 10:51 AM
Zack...I didn't see anything in the attachment except the data I posted. Am I missing something? Thanks.

Zack Barresse
07-28-2004, 11:09 AM
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
07-28-2004, 11:13 AM
I get a bunch of #NAME? errors when I run the macro.

Zack Barresse
07-28-2004, 11:15 AM
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 (http://longre.free.fr/english/index.html). You could always substitute some kind of Advanced Filter for Unique Values if you'd like. I just took the route I knew.

babsc01
07-28-2004, 11:27 AM
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
07-28-2004, 11:34 AM
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
07-29-2004, 08:33 AM
Yeah, this one can be solved...you took me in the right direction. Thanks.