+ Reply to Thread
Results 1 to 9 of 9

Filtering Data Using not Exact Match

  1. #1
    Registered User
    Join Date
    01-19-2022
    Location
    Preston, England
    MS-Off Ver
    Office365
    Posts
    11

    Filtering Data Using not Exact Match

    I am trying to extract / filter a list of names based on if a player is attending. For example if Player A is FALSE not playing only show the last 3 pairs highlighted in the example below.

    I have a list of names of players. Column A (Players) Column B (Attending T / F)

    - Player A - FALSE
    - Player B - TRUE
    - Player C - TRUE
    - Player D - TRUE

    I then have a list of pairs for example. Column D (Pairs not played)

    - Player A & Player B
    - Player A & Player C
    - Player A & Player D
    - Player B & Player C
    - Player B & Player D
    - Player C & Player D


    I have tried a few different ways but cannot see a way round it.

    I thought about using IF, IFERROR, VLOOKUP, INDEX and MATCH.


    Any ideas?
    Attached Files Attached Files
    Last edited by Terrortot38; 02-02-2022 at 01:44 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Filtering Data Using not Exact Match

    You may want to investigate Advanced Filter. Where you can have a Criteria Matrix

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Filtering Data Using not Exact Match

    You need Conditional formatting. Anyway Pl read the yellow banner on top of this page.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    01-19-2022
    Location
    Preston, England
    MS-Off Ver
    Office365
    Posts
    11

    Re: Filtering Data Using not Exact Match

    Hi, I have now attached a sample workbook. I did not know this was mandatory to be included.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Filtering Data Using not Exact Match

    Perhaps the following will help.
    1. Make a list of those not attending by putting the following in cells H2:H21 =IFERROR(INDEX(A$2:A$21,AGGREGATE(15,6,(ROW(A$2:A$21)-ROW(A$1))/(B$2:B$21=FALSE),ROWS(H$2:H2))),"")
    2. Indicate those pairs that should be filtered out by putting the following in cells E2:E41 =SUMPRODUCT(--(ISNUMBER(SEARCH(H$2:H$21,D2))*(H$2:H$21<>"")))>0
    Note that columns D:E could be filtered directly by selecting the filter/sort button in cell E1 and choosing only False, OR...
    3. Make a list of pairs that are available by putting the following in cells J2:J41 =IFERROR(INDEX(D$2:D$41,AGGREGATE(15,6,(ROW(D$2:D$41)-ROW(D$1))/(E$2:E$41=FALSE),ROWS(J$2:J2))),"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Filtering Data Using not Exact Match

    Adding a file is not mandatory, but it does mean that :

    a) we do not have to re-type the stuff that you have in front of you,

    b) it usuallly tells us what we need to know.

    Try this though... (maybe???)

    =INDEX(D:D,AGGREGATE(14,6,ROW($D$2:$D$41)/(ISERROR(SEARCH(A2,$D$2:$D$41))),{1;2;3}))

    But not in this case. This MAY be the answer, but I am not sure, as a non-working formula is of limited help. What REALLY helps are manually calculated expected answers...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Filtering Data Using not Exact Match

    Select D2:D41.
    Home--> Conditional Formatting--> New rule
    Enter the formula for CF

    =SUM(1*ISNUMBER(FIND(IF($B$2:$B$21=TRUE,$A$2:$A$21,"ZZZZZZ"),$D2)))=2
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 02-05-2022 at 05:12 AM.

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Filtering Data Using not Exact Match

    To get the required list.
    In J2 then copy down
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Filtering Data Using not Exact Match

    I suspect the OP has gone, never to return (Elvis has left the building)... but I was looking at this again and noticed a few mistakes in the initial data.

    It's a dreary day in Belfast... pouring with rain... so I tinkered with this a bit.

    Terrortot, if you ever revisit the forum.... how does this look? It assembles all possible player combinations, and then selects only the valid ones...
    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. filtering exact match figures with opposite sign
    By yutoyumi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2017, 04:23 AM
  2. To Sort exact and partial exact match for a single column.
    By Jagdev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2014, 05:08 AM
  3. Advanced Filtering from one sheet to another not working for exact match
    By thelisa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2013, 09:57 AM
  4. [SOLVED] Look up a value & text combination where the value is not an exact match in data table
    By Kraftyk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2013, 07:55 AM
  5. Finding the Big and Exact Match of Sample Data
    By hamijami in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2012, 08:55 AM
  6. [SOLVED] How to find data with the help of VLOOKUP even though it is not exact match?
    By rajani.indegene in forum Excel General
    Replies: 3
    Last Post: 04-25-2012, 02:37 AM
  7. Match EXACT data
    By Mark1959 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2009, 06:44 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