Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    In dire need for help with Searching in list...

    Hi Guys,

    I arrived to the point where I can't put the search part of any longer in my excel file. After long consideration I decided against lists and I want to use search to find the item I mean when I enter information in the cell using the knowledge of how I would enter it every single time. I tried to come up with the most complicated item name that has the most variants so that it would cover all scenarios hopefully.

    In the following example, when I enter partial item names into [Sheet1:ColumnA:Cell 1,2,3 etc] (right), I want to search for a match on Sheet2:ColumnA and return the item found into the same cell, overwriting the originating cell. I have done something similar using VLOOKUP but it is limited as when entering "Freelancer" it would return "Digital Freelancer" as the first item name that includes the word "Freelancer".

    Example:

    Code:
    Item name                 <>    User input for Search
    Digital Freelancer        <>    "D Free"
    Digital Freelancer DUR    <>    "D Free DUR"
    Digital Freelancer MAX    <>    "D Free MAX" 
    Digital Freelancer MIS    <>    "D Free MIS"
    Freelancer                <>    "Free"
    Freelancer+               <>    "Freelancer+" or "cer+"
    Freelancer DUR            <>    "Free DUR" 
    Freelancer MAX            <>    "Free MAX"
    Freelancer MIS            <>    "Free MIS"
    Freelancer MIS Upg.       <>    "Free Mis Upg" or "MIS Upg"
    Physical Freelancer       <>    "P Free"
    Physical Freelancer DUR   <>    "P Free DUR" or "P F DUR" 
    Physical Freelancer MAX   <>    "P Free MAX" or "P F MAX"
    Physical Freelancer MIS   <>    "P Free MIS" or "P F MIS"
    Thanks for looking...
    Ferenc
    Last edited by Ferenc Nagy; 2015-08-10 at 16:47.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    I'm not exactly sure what you are asking but here's my take on what I think you're asking?

    You can use wildcards to match any of the items by just supplying enough information for an exact match, e.g.

    To find "Physical Freelancer MAX" you would search for "Phy*Fre*MA"

    Here's a little function that does the work so you can test it out.
    Code:
    Option Explicit
    
    Function WCFind(zFindWhat As String)
    '
    ' WCFind Macro
    '
    
    '
        Cells.Find(What:=zFindWhat, _
                   After:=ActiveCell, _
                   LookIn:=xlValues, _
                   LookAt:=xlPart, _
                   SearchOrder:=xlByRows, _
                   SearchDirection:=xlNext, _
                   MatchCase:=False, _
                   SearchFormat:=False).Activate
                   
       MsgBox "Your value match was found in Column " & _
               ActiveCell.Column & " Row " & _
               ActiveCell.Row, _
               vbOKOnly, "Pattern Matched"
            
    End Function
    Sample Run:
    Ference.JPG

    If this isn't what you are after let us know. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    This looks exactly like what I am looking for. Will do some testing with it

  4. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi RG,

    Could you take a look and maybe tell me what I did wrong in this test file?

    Thank you
    Ferenc

    Book2.xlsm

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ference,

    Just what do you want to be returned into Sheet1 Col A. The found string or it's address?

    I have a tennis date to make so I'll get back to this when I get back.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    The correct Item name...

    I enter search phraze in A1 and want to return what was found into A1 as well

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Ok, here's my stab at getting you what you want. I think have it error checked (for invalid patterns). I changed things around so the function is as generic as possible and the setup is done in the Worksheet_Change module.

    Test File: FerencTestRGv1.xlsm

    Note: If you want to erase the values in Col A you need to turn off event processing with: Application.EnableEvents=False
    Once you've cleared the values use Application.EnableEvents=True to reenable events so the Worksheet_Change event procedure will work. You can enter these commands in the VBE Immediate Window.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi RG,

    Thank you for the example file, now I understand it a lot more. I have one behavior that I would like to change.
    When entering "D*Free" it will return "Digital Freelancer" but if I enter it again - in the same cell or any other - it will now return the next item in line that has "D*Free" in it, so I get "Digital Freelancer DUR", and on next I get "Digital Freelancer MAX" etc. How can I reset the search after each search?

    Thank you
    Ferenc

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    That change can be accomplished by making 2 changes:

    WCFind Function:

    Change This: After:=ActiveCell, _
    To This: After:=Cells(1,1), _

    On Sheet 2:

    Add a row at the top for a Title:
    FerencTitle.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi RG,

    I am at a loss, the function is unreliable...

    After making the change you suggested, for the term "Free" I get "Digital Freelancer" every single time when the response should be "Freelancer" every time...

    Could you please take a look at the file?

    Thank you
    Ferenc


    FerencTestRGv1.xlsm

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Ah there's the rub!

    Unfortunately, that's the way Excel VBA Find works.

    I played around with Regular Expressions but I'm not familiar enough with them to get the right patterns and I'm sure they would play havoc with your users anyway.

    However, what you can do to make the search work the way you want is to rearrange your titles as follows:
    FerencTitles.JPG

    Now the simple search patterns will return the correct values. (Note: The order they are in doesn't really matter it is the way the titles are structured that counts)
    FerencResults.JPG

    I know some times management can be a stickler for things like this but just explain to them that you'd be happy to make it work (using current titles) as long as they are willing to spend the time and money to get it "just the way they want it" vs this 5 minute fix.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you RG for trying.

    I am the only user and management as well... I am doing this as a passion project to make my own work easier, so I know exactly my own patterns when entering an item...
    I was wondering, would it be possible to tell excel to look for each word separately and use the order of them as well? I know this doesn't make much sense yet so let me try explaining it in more details:
    Worst case scenario, my search term would have 3 parts. Each part is separated by space or *.
    Using the same sample item names with 2 new additions, I should be able to demonstrate all scenarios:
    Code:
    Digital Freelancer
    Digital Freelancer DUR
    Digital Freelancer MAX
    Digital Freelancer MIS
    Freelancer
    Freelancer+
    Freelancer DUR
    Freelancer MAX
    Freelancer MIS
    Freelancer MIS Upg.
    Physical Freelancer
    Physical Freelancer DUR
    Physical Freelancer MAX
    Physical Freelancer MIS
    Vanduul Scythe
    Vanduul Glaive
    Example 1: P*Free*DUR
    Code:
    Part 1 - "P"
    Part 2 - "Free"
    Part 3 - "DUR"
    The parts also represent the order of the search, in case of a 3 word search it would be easy, because the maximum word count for an item is also 3, so if we find the names that have P in the first word, Free in the 2nd word and DUR in the 3rd word, we would have a perfect match.

    Example 2: D*Free
    Code:
    Part 1 - "D"
    Part 2 - "Free"
    This search would return 4 possible results and we would need to tell excel to always use the first possible result, so it would "ignore" possible 3 word results. [This requires the item name list to be sorted in alphabetical order, which it is done already so no problem there.]

    Example 3: Free
    Code:
    Part 1 - "Free"
    This search would return 6 possible results and we would need to tell excel to ignore multi word results, or just use the first possible result, assuming we used a search that would only check for match in the first word of the item name.

    Example 4: Glaive
    Code:
    Part 1 - "Glaive"
    After looking for a match in each items first word, because of no result, we would check for match in 2nd words of item name and voila, a match would emerge. Again we would use the first possible match resulting in the exact behavior that is desired.

    Please let me know what do you think of this...
    Thank you
    Ferenc

    PS: Anyone else is welcome to share opinions or ideas as well...
    Last edited by Ferenc Nagy; 2015-08-12 at 03:30.

  13. #13
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I found a solution to the problem.
    Found something similar, and with a little bit of tweaking I managed to get the behavior I wanted.

    Thanks
    Ferenc
    Attached Files Attached Files

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Glad you found a solution. I didn't see this until I had worked up another possibility so I thought I'd post it for anyone who might be interested.

    This uses a two column titles list:
    TwoCols.JPG
    The search is done using the first column and the result comes from the second column. Thus, maintaining your desired titles while controlling the search to work properly. Note: the comment I made in an earlier post about order not mattering was incorrect col A MUST be in sorted order.

    Test Screens:
    You get this if you enter an search that matches something in column 2!
    WrongColumn.JPG
    Final Test Results:
    WrongColumn2.JPG

    Test File: FerencTestRGv3.xlsm

    Note: I included a file as the Search Module changed.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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