1. Welcome! Please take a few seconds to create your free account to post threads, make some friends, remove a few ads while surfing and much more. ClutchFans has been bringing fans together to talk Houston Sports since 1996. Join us!

EXCEL/VBA Problem - Help needed - much appreciated

Discussion in 'BBS Hangout' started by hotblooded, Dec 4, 2008.

  1. hotblooded

    hotblooded Member

    Joined:
    Sep 12, 2006
    Messages:
    1,346
    Likes Received:
    3
    Hi excel experts,

    I wanted to post this during work, but sadly this site got blocked.

    Please bare with me

    Essentially, I have a database thats is something like this

    ColumnA ColumnB
    Name NBA Team
    Clutch Rockets
    Dr. Dunk Rockets
    R2k Rockets
    Bandwagoner Rockets
    DD Rockets
    Bandwagoner Jazz
    Bandwagoner Mavs


    Okay, so on another tab, I want something whereby a User can type in the name of anyone of the above and it will return with NBA team(s) the person go for.

    A simple Vlookup would not do because it would just pick up say the first time it comes across the person called "bandwagoner" and returns "Rockets"

    I want something that is user friendly that can when you type "Bandwagonre" into a designated cell, All three teams he is associated with comes up under the "Team" Column.

    I tried to use Macros, but pretty much got stuck from the get go.

    Can someone ENlighten me please?

    I really appreciate the help guys.

    Thanks
     
    #1 hotblooded, Dec 4, 2008
  2. Master Baiter

    Master Baiter Member

    Joined:
    Jul 6, 2001
    Messages:
    9,608
    Likes Received:
    1,376
    I'm not exactly sure how to go about doing it and while I could figure it out if I put some effort into, what I think you are wanting to do is make a query based upon what the user inputs.

    Maybe that will push you in the right direction.
     
    #2 Master Baiter, Dec 4, 2008
  3. JeopardE

    JeopardE Member

    Joined:
    Jun 29, 2006
    Messages:
    7,418
    Likes Received:
    246
    Yep, this sounds a lot more than a SQL problem than an VBA problem.

    SELECT NBATeam FROM Teams WHERE Name =
     
    #3 JeopardE, Dec 4, 2008
  4. francis 4 prez

    francis 4 prez Member

    Joined:
    Aug 15, 2001
    Messages:
    22,025
    Likes Received:
    4,552
    you just want something that goes through the name column and finds all instances that equal your designated cell and returns the team name? so like:

    j=0

    for i = 1 to num 'with num being however many rows you have

    if sheet1.cells(1+i,1)=sheet1.cells(x,y) then ' with cell(x,y) being wherever you type the name and cell(1+i,1) being the column with people's names

    sheet1.cells(x+j,y+1)=sheet1.cells(1+i,2) 'puts team name in cell next to designated cell

    j=j+1 'will allow next team name to be put in row below first team name

    end if

    next


    i'm sure there's a cooler way to do it but that's how i would do it and i think that should work.
     
    #4 francis 4 prez, Dec 4, 2008
    Last edited: Dec 4, 2008
  5. JuanValdez

    JuanValdez Member

    Joined:
    Feb 14, 1999
    Messages:
    35,057
    Likes Received:
    15,231
    You can still do it with vlookup, sorta. Put in another column for Iteration. It puts a counter on the number of times a particular name comes up by using countif on the cells above. So the first Bandwagoner would become Bandwagoner1, the second Bandwagoner2, etc. Your output section would then have a series of answers. So when someone typed Bandwagoner in A2, then B2 would do a lookup on Bandwagoner1, B3 would lookup on Bandwagoner2, and so on. You can use an if/then statement to make it blank where a vlookup returns an N/A.
     
    #5 JuanValdez, Dec 4, 2008
  6. hotblooded

    hotblooded Member

    Joined:
    Sep 12, 2006
    Messages:
    1,346
    Likes Received:
    3
    Thanks guys,

    My original way was a dumb way

    using flags in Excel since I am better with Excel than VBA

    Ie having a match function returning a 1 if the name in each of the column matches those that the person typed in.
    next to that have a cumulative sum function so if there are 3 occurences the column wil look like

    0 Dr Dunk
    1 Bandwagonner
    1 Clutch
    2 Bandwagoner
    2 DD
    3 Bandwagoner


    then i will have to physically have a V look up where where it goes "Vlookup critieria 1..2 ...and 3" obviously this restricts it to how many I will do as oppose to pickig up everything


    Is there a way to make it so that if the person typed in the wrong name..it will pick up the closest match??

    Thanks
     
    #6 hotblooded, Dec 5, 2008
  7. Jugdish

    Jugdish Member

    Joined:
    Mar 27, 2006
    Messages:
    9,081
    Likes Received:
    9,591
    You should just ask people what teams they like.
     
    #7 Jugdish, Dec 6, 2008
(You must log in or sign up to post here.)

Share This Page