+ Reply to Thread
Results 1 to 9 of 9

Dynamic Filter Formula w/criteria list

  1. #1
    Registered User
    Join Date
    12-24-2018
    Location
    NC, US
    MS-Off Ver
    office 2016
    Posts
    12

    Dynamic Filter Formula w/criteria list

    Hello,

    I am trying to learn and implement some new formulas.

    Lets say I have a raw data table that contains transaction data with 25+ columns of unique transaction information. One of those columns list the customer name/ID. I have a separate table that is a list of all customers determining their tax exempt status (Yes or No). My goal is to in a separate worksheet, use the filter function spilling only the rows where the customer name/ID = "Yes". I am struggling to put this into a formula. If there is another thread that answers this questions, please forgive me as I couldn't locate it.


    Thanks,
    Chris

  2. #2
    Registered User
    Join Date
    02-26-2020
    Location
    US
    MS-Off Ver
    2010
    Posts
    152

    Re: Dynamic Filter Formula w/criteria list

    Please provide sample workbook it helps us to test formulas and have a better understanding of your specific requirements.

  3. #3
    Registered User
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    201

    Re: Dynamic Filter Formula w/criteria list

    Hi,
    just worked this out from scratch (!).
    you can use this formula:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; 03-27-2020 at 11:38 AM. Reason: minor update
    How to learn VBA: RECORD A MACRO ... and study it!

  4. #4
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Dynamic Filter Formula w/criteria list

    Using the new dynamic array formulas, here's a different take on a solution. I have changed the file that was attached to show multiple records per customer because I assumed that's what you are expecting (as RChad says, it would be better if you attached a mocked up file with expected results - please read the yellow banner at the top of this page).

    Anyway, the nice thing about the new dynamic array formulas is that you enter one formula in one cell and the whole result appears (no copying formulas down or across). the formula would be:

    =FILTER(A2:C26,XLOOKUP(A2:A26,$E$2:$E$15,$F$2:$F$15,"Not found",0,1)="yes","Tax Exempt Status not found")

    This is entered in cell H3 of the attached.
    Attached Files Attached Files
    Last edited by Gregb11; Yesterday at 12:02 PM. Reason: change text
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Registered User
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    201

    Re: Dynamic Filter Formula w/criteria list

    @Gregb11,
    these functions are not available in Office 2016, I see in front of the formula's : _xlfn._xlws

    Cheers
    Erwin

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    6,447

    Re: Dynamic Filter Formula w/criteria list

    They're only available in 365 on the monthly channel.
    But that is what the OP asked for, so suspect their profile is out of date.

  7. #7
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Dynamic Filter Formula w/criteria list

    Hmm, I thought for sure I saw the OP was on Office 365, but maybe I got confused because he was asking about the new formulas, which are only available in O365. Oh wait, I think the confusion is that this forum section is called "Office 365", so I would expect the answer to be based on O365.

  8. #8
    Registered User
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    201

    Re: Dynamic Filter Formula w/criteria list

    Yep, my bad, I've overlooked that.

    Cheers
    Erwin

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    6,447

    Re: Dynamic Filter Formula w/criteria list

    Must admit I hadn't noticed this was in the Office 365 board.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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