+ Reply to Thread
Results 1 to 10 of 10

Using Advanced Filter: problem with using wildcards

  1. #1
    Registered User
    Join Date
    01-20-2016
    Location
    288
    MS-Off Ver
    2016
    Posts
    29

    Using Advanced Filter: problem with using wildcards

    I'm working with 6 columns of data and 1000 to 300,000+ rows. Column A (Company name) Column B (address) Column C (city) Column D (zip) Column E (phone) Column F (type of company). I'm using Column J for my keyword filter set.

    I'm cleaning up a database of construction companies. I'm trying to shrink the list by filtering by business names to eliminate everything that is not construction based. I get the jest of how the filter works, but the wildcards are messing with me. I'm having difficulty eliminating certain words. For example, I try to eliminate the words "law" and "lawyer", but keep the word "lawn".

    I tried '=law and '=lawyer? but it doesn't work. It's happening with other words as well. Like "realty" and "body". I think i figured out that using two asterisks along with the equal sign, that they basically cancel each other out. '=*word* is that correct?

    So far i've got 250 something keywords i want to filter for. The further i get into it, the more words i add to the list. The list i was handed when i started the position a month ago was 50 words long.

    So basically, i need help fixing the word difference problem.
    I'm trying trying to be more efficient, but the more i try, the less efficient i get. :-/

    I've attached the company and keyword sheet.

    I appreciate any help that anyone can provide.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: Using Advanced Filter: problem with using wildcards

    Hi, welcome t0 the forum

    If I understand you correctly, you need to put the wild card in the formula that you are using to search for the word, not the word itsself.

    What exactly are you testing against what?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-20-2016
    Location
    288
    MS-Off Ver
    2016
    Posts
    29

    Re: Using Advanced Filter: problem with using wildcards

    I'm confused.

    But i'll try and answer you. I have the keyword list that i'm using to scrub the business info list for unwanted companies. Take keyword list and throw them in column J on the Business Info sheet and use same name as column A (as i'm wanting to sort the list based on info in the company name). I need to figure out how i can get all variations of a single word/or phrase.

    Does that make any sense what so ever?

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Using Advanced Filter: problem with using wildcards

    find attached see if this you want

    You will find matched column that can be filtered to see the matches and can be copied.
    formula in matched column is
    =SUM(IFERROR(SEARCH("*"&$L$1:$AG$1&"*",A2),0))
    where ever there is a match of any key word it gives one else gives 0
    (Above formula is array entered means copy paste the formula then hold control and shift together and then hit enter)

    L1:G1 is the keyword you are searching for.

    Attached Files Attached Files
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  5. #5
    Registered User
    Join Date
    01-20-2016
    Location
    288
    MS-Off Ver
    2016
    Posts
    29

    Re: Using Advanced Filter: problem with using wildcards

    Thanks for the help.

    I'm not familiar with Array's. Will this help distinguish between "law" "lawyers' and "lawn"? the way i was going about it, when filtering for law and lawyers, it would pick out lawn as well and i'd have to go through 10k+ records and sort through by hand. Does that make sense?

    Also, could you reccomend where i could find more information on array's so that i can figure out exactly how the above formula works?

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Using Advanced Filter: problem with using wildcards

    in case of distinguishing use below formula
    =SUM((TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",100)),100*ROW(INDIRECT("1:"&LEN($A2))),100))=$L$1:$AG$1)*1)
    copy paste below then hold control and shift together and then hit enter to make it array formula

    as your company name is separated by spaces above formula separates each word and then check against the key word list that is the case law will equal law not the lawyer and not the lawn

    to see how this formula works select the cell after entering formula then go to formula evaluation tab and check
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-20-2016
    Location
    288
    MS-Off Ver
    2016
    Posts
    29

    Re: Using Advanced Filter: problem with using wildcards

    alright, i am now thoroughly confused. I tried copy and pasting the formula, but it just gives me a value error. i'm not even sure how it's supposed to work. do i still have to select all of column a? or do i just copy and paste the formula in any cell and it will do it's thing.

    My head hurts.

  8. #8
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Using Advanced Filter: problem with using wildcards

    see the attachment, I have cleared as much things as I think, are confusing you.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-20-2016
    Location
    288
    MS-Off Ver
    2016
    Posts
    29

    Re: Using Advanced Filter: problem with using wildcards

    Hemesh,

    THANK YOU! you are a rockstar.

    It works great.

    Hope you have a wonderful weekend!


  10. #10
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Using Advanced Filter: problem with using wildcards

    You are welcome rob. You can also thank by clicking the add rep icon below the bottom left corner of post

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advanced Filter Problem
    By M_Easty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 06:42 PM
  2. Advanced Filter Problem
    By dhaval123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-27-2013, 02:17 AM
  3. Advanced Filter problem
    By Andrew-Mark in forum Excel General
    Replies: 6
    Last Post: 05-21-2008, 09:28 AM
  4. Advanced Filter problem
    By LisaK in forum Excel General
    Replies: 1
    Last Post: 04-03-2008, 11:09 AM
  5. Problem with Advanced Filter
    By ice_breaker in forum Excel General
    Replies: 3
    Last Post: 10-02-2007, 10:35 AM
  6. [SOLVED] Wildcards in Advanced filter criteria don't work with Excel 2003
    By Inconceivable in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2005, 10:05 PM
  7. [SOLVED] Advanced Filter problem
    By SB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2005, 09:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1