+ Reply to Thread
Results 1 to 5 of 5

Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

  1. #1
    Registered User
    Join Date
    09-04-2021
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    12

    Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

    I have an Excel index to organize information for a collection of photographs. See the attached sample worksheet.

    Because one photo may have more than one person in it, I have created multiple columns for their names (Person1, Person2, Person3, etc.)

    I want to be able to filter the spreadsheet to display only those rows that contain the name of a particular person. My problem is that the person's name may be located across MULTIPLE possible columns (Person1, Person2, Person3, etc.).

    Can the "Advanced Filter" feature be successfully be used for this? If not, is there another way to accomplish this?

    APPRECIATE IT!!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

    Here's a simple way of achieving that using a couple of formulae. First of all, I've added a new sheet and used B1 in the new sheet to allow you to enter a name. This must be a full name, exactly as entered in the other sheet.

    Then I have used column K on Sheet1 as a helper column, with this formula in K2:

    =IF(COUNTIF(E2:I2,Sheet2!$B$1),MAX(K$1:K1)+1,"-")

    Copy this down to the bottom of your data (or beyond, if you intend to add more data in the future). This formula identifies those records which match the criteria, i.e. the name being searched for exists in one of the columns E to I.

    Then you can use this formula in A4 of Sheet2:

    =IF(ROWS($1:1)>MAX(Sheet1!$K:$K),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$K:$K,0)))

    and this can be copied across to D4, and then those formulae copied down as far as you think you may need them (I've copied to row 15 in the example file).

    Change the name in B1 (the yellow cell) and the display will automatically update.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-04-2021
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

    This is a BRILLIANT solution! And it seems to be much quicker than the "Advanced Filters" that I was attempting. THANK YOU very, very much!


    P.S. I am sorry for the "double post," but I am new and did not notice the "Formulas and Functions" category until I had already posted in "General."

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

    Most of us read threads from all the sub-forums, so you don't need to be too concerned with where it goes, although you can always ask a Moderator to move a thread into a different sub-forum for you.

    Glad you like the solution - if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  5. #5
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,289

    Re: Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

    In attachment using advanced filter. For your convenience, I have added a list of names and an event procedure.

    You are probably aware that someday you will come across a photo with more people than the defined columns and that the person you are looking for is not the one in the photo (duplicate names).

    Artik
    Attached Files Attached Files

+ 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. Replies: 2
    Last Post: 09-05-2021, 12:12 AM
  2. Trying to "Reset" advanced filter on multiple sheets with one button.
    By Cpayne19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2020, 01:15 AM
  3. advanced and complex "fuzzy" filter on multiple criteria
    By Ebru in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-03-2016, 03:11 PM
  4. Replies: 7
    Last Post: 03-17-2014, 01:51 PM
  5. [SOLVED] Emulate Excel 97 getopenfilename "Advanced" find including "Search subfolders"
    By FORTRANguru in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 08:21 PM
  6. [SOLVED] advanced filter criteria "begins with" and "does not begin with"
    By Eddie O in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  7. [SOLVED] "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du
    By TC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-11-2005, 10:06 PM

Tags for this Thread

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