Results 1 to 9 of 9

Thread: How to filter out records and dependency on other column

  1. #1

    Lightbulb How to filter out records and dependency on other column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    How to filter out records and dependency on other column?
    I have attached a sample data to elaborate my problem.

    Assumption: the source data cannot be modified.
    I want to show the FAMILY which have at least 1 "F" in SEX column.
    Attached Files Attached Files

  2. #2
    Add a column to the data with a header of say Test and a formula of

    =COUNTIFS($A$1:$A$14,A2,$C$1:$C$14,"F")>0

    Then add Test to report filter on the pivot and filter for True

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Add a column to the data with a header of say Test and a formula of

    =COUNTIFS($A$1:$A$14,A2,$C$1:$C$14,"F")>0

    Then add Test to report filter on the pivot and filter for True
    Sorry to inform you that I cannot modified the data source.
    I connect as external data source since it is modifying regularly so that I will not copy to local.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by whoiswct View Post
    Sorry to inform you that I cannot modified the data source.
    So why not retrieve it to an Excel table. It will still update for you when you open the workbook. Base your PivotTable off the table in the workbook, and you can do what you like to that data source...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Quote Originally Posted by Ken Puls View Post
    So why not retrieve it to an Excel table. It will still update for you when you open the workbook. Base your PivotTable off the table in the workbook, and you can do what you like to that data source...
    If I retrieve to an excel table, it will become a offline data only but not a live data.
    The source excel data is modifying every day.

  6. #6
    So you retrieve it every day. That is an easily automated task.

    What version of Excel are you on?

  7. #7
    excel 2010

    Sent from my phone using Tapatalk

  8. #8
    The I suggest that you use PowerPivot to query the data source and manage it there.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm thinking that this is heavily related to this thread. Let's keep the replies there until we sort the base issue out.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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