Consulting

Results 1 to 9 of 9

Thread: Solved: To SUBTOTAL or not to SUBTOTAL?

  1. #1
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location

    Solved: To SUBTOTAL or not to SUBTOTAL?

    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!!!

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

  3. #3
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Zack...I didn't see anything in the attachment except the data I posted. Am I missing something? Thanks.

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

  5. #5
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    I get a bunch of #NAME? errors when I run the macro.

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

  7. #7
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    TOO COOL, Zack. Looks like I'm gonna try that direction. The rest of it is just using COUNTIF to label the players. THANKS!!!!

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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?)

  9. #9
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Yeah, this one can be solved...you took me in the right direction. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •