View original (French)

Search for multiple words at once in a single sheet

Solved
AdrianO89 Posted messages 8 Status Member -  
AdrianO89 Posted messages 8 Status Member -
Hello,

I would like to search for several words at the same time in a single Excel sheet, but I can't do it with "ctrl+F" as it only allows me to search for one word at a time.

In my specific case, I have an Excel sheet (1) with company names in column A.

In another file (2), I have company names in column F that I need to compare with the file (1) to see if they are already present.

Right now, I am spending a lot of time searching for each name one by one. I copy the name from file (1), then ctrl+F, paste the name in the search bar, go to file (2), and click search to see if the name is already there.

This is a very lengthy task when I have to do it for several files with about 50 lines...

Can you recommend another method that would allow me to easily compare all the names from file (1) to those in file (2) at once?

Please note that I am a novice and my knowledge of this tool is very limited.

Thank you in advance for your responses :)

1 answer

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Good evening,

    Assuming the list to compare is in Sheet2, column F (if it’s in another file, copy and paste it in Sheet2) with the original list in column A of Sheet1:
    In a column next to F (in G for example); put the following formula in the first cell:
    =IF(COUNTIF(Sheet1!A:A,Sheet2!F1)=0,"not listed","listed")
    and drag down the column (double-click on the small black square at the bottom right of this cell G1)
    The formula counts how many times it finds a name in column A of Sheet1; if the result is 0, then it definitely does not appear in the list

    To adapt to your file

    Best regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. AdrianO89 Posted messages 8 Status Member
       
      Thank you for your quick response. I followed your method to the letter, and I have no numbers displayed, just "is listed" or "is not listed."

      If I understood correctly, if it says "is listed" in column G of Sheet2, it means the name is indeed in column A of Sheet1.

      The problem is that upon checking, there are names that the formula identifies as "is listed" even though they are not in the list in Sheet1, and vice versa.

      I also have duplicates; the names are sorted from A to Z in Sheet2 (which is not the case in Sheet1), and for the same two names, it says "is listed" once and "is not listed" once.

      Do you know what might have happened and how to fix this?
      0
      1. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > AdrianO89 Posted messages 8 Status Member
         
        Hello

        Without seeing your file, it's difficult to say (to attach a file, upload it on cjoint.com and come back here to provide the link given)

        Yes, if the IDENTICAL name is found in column A of sheet1, it will display the mention 'is found' (or any other text you would put in the formula)
        A name that differs by one letter or, worse, a space placed before or after and not visible at first glance is not identical, which is what should happen in your lists

        Best regards
        0
      2. AdrianO89 Posted messages 8 Status Member > via55 Posted messages 14393 Registration date   Status Member Last intervention  
         
        Hello via55,

        Here is the link to the file: https://www.cjoint.com/?0Cdrgbk81js

        My comparative list is in Sheet1, and the one to compare is in Sheet2, and I pasted the formula in Sheet2 Column G as you had previously indicated.

        Apparently, the names are indeed written the same way, for example the company name "KEOLIS EST EPINAL" which appears 3 times, is correctly listed in Sheet1 when checked with CTRL+F, but in Sheet2 Column G it says once "not found" and twice "found".
        0
      3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > AdrianO89 Posted messages 8 Status Member
         
        Hello

        You started in line 2 without adapting the formula and put in G2:
        =IF(COUNTIF(Sheet1!A:A,Sheet2!F1)=0,"not included","included")
        whereas it should be:
        =IF(COUNTIF(Sheet1!A:A,Sheet2!F2)=0,"not included","included")

        As a result, the mentions of included or not are shifted by one line!

        Best regards
        0
      4. AdrianO89 Posted messages 8 Status Member > via55 Posted messages 14393 Registration date   Status Member Last intervention  
         
        Alright, I understand my mistake, this will greatly facilitate this usually very long job!

        Thank you so much for the formula, the explanations, and your quick responses, they're perfect :)

        Wishing you the best and hope to see you again soon on other topics

        AdrianO.
        0