Consulting

Results 1 to 5 of 5

Thread: Interactive Way to Search Based on First Few Characters Typed

  1. #1

    Interactive Way to Search Based on First Few Characters Typed

    Hi everyone,

    I was hoping to get some feedback on a scenario my manager had. Our accounting system is quite old, and our sales team has trouble looking up skus/products, and sometimes enters the wrong sku, causing them spend extra time on activities.

    My manager was wondering, if they just typed the first couple of letters, such as the word "orange" could it populate all the matching descriptions and skus so they could just pick from the list and it would give them the sku they're looking for? This would be similar to using a filter, or the google search bar, just more interactive. This is because they're not excel savvy, clearing the filter is a challenge for them.

    I have attached a sample list, typing "Orange", would give me
    WIRENUT 30-073 ORANGE 73B,
    SCOTCH 35 1/2x20 ORANGE
    SCOTCH 35 3/4 X 66 ORANGE, etc.

    so if they picked one, it would return the description plus sku.


    Just wanted people's thoughts or any ideas people had.

    Thanks again for your time and consideration.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    This functionality is built-in to autofiltering:
    2019-11-12_185859.jpg
    Click on the Stock Description down arrow (autofilter), type orange into the search field, then either:
    untick (Select All Search Results) and scroll down and tick the one you want,
    or:
    click OK and do similar with the Stock # dropdown.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi JackChang!
    Not sure, Please refer to the attachment.


    --Okami
    Attached Files Attached Files

  4. #4
    Thanks p45cal and Okami!

    I also think using the standard filter is a simple way to find the sku's they're looking for. My manager was thinking I could somehow put it in a pivot table and have the users select the slicer after typing a couple of letters, but I don't think that's possible.

    Okami thanks this works good! I also e-mailed this to him and see what he thinks, have a great weekend everyone!!

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    >27,000 Items in ONE inventory list!!!
    As Judge Judy would exclaim, "Reeee DICK you luss!"

    And how are your $Users supposed to filter for Item 0.05.E.35K.10.100.1.54.

    At the very, very least add a "Category" or "Type" Column. I would also add "location" or "Bin_Number", "Qty_In_Stock", Min_Stock_Required", "Max_Stock_Allowed" and "Standard_Order_Qty" Columns, but that's just because I once had to maintain inventories.

    27K items on one sheet is starting to push the limits of Excel, I would break the Categories into multiple sheets. Examples of Sheet CodeNames: Power_Wire, Control_and_Data_Cable, Conduit_and_Trays, Meters_And_Sensors and similar. Note that in any such organization, there will be a need for Misc_and_Uncategorized, However that will be a favored destination for many things that really belong in presorted/categorized; Periodically, you will want to check the count in there and limit it to a smallish number like 20 items. Note that the Category,Type and various descriptive columns will only contain values that make sense to that sheet

    Each such sheet should have several columns as mentioned above. Columns that make sense to your technical users. Using a system like this means that with a UserForm, a $User only has to make a few mouse clicks to return a small sorted list that can be selected from with a few characters typed in. It also means that an Item like 0.05.E.35K.10.100.1.54 can be visually located in just a few seconds.

    Note that, IMO, every sheet should have the "Stock Clerk" columns, but that's just me.

    Generally, if a combination of categories or type of items on any given sheet count more than a few hundred, some of those Types should be moved to a different sheet. For example, if you had a hundred+ Wire-nut types they should be on their own sheet.

    The idea is that a $User should, on the UserForm, be able to click WireNuts, Red, 50 Pack, type 10 into the Quantity box then click the Add Item Command and have that all added to the invoice and/or the pick-list.

    For Wire, they might click 00Ga, Direct Burial, 3 conductor, Grounded Sheath, Armored, 500ft Roll


    This is a big project for you. If your $Boss will approve the budget, check out our Paid services on our home page: http://www.vbaexpress.com/
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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