Results 1 to 8 of 8

Thread: filter (2003)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter (2003)

    I have a spreadsheet that has client number, vision members, FA contracts, FA members, Dental Members
    I would like to filter the spreadsheet to show records for varying combinations of Vision members, FA contracts, FA members, Dental Members.
    For example, I would like only records where there are vision members and FA Contracts and the other two fields are null. I would like to see records where none of the fields are null etc. Can this be done in Excel. I know I can make queries in Access but the people who are using the spreadsheet, are Access challenged. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter (2003)

    Make sure that you have column headings (field names) above the data.
    Click in any cell of the table.
    Select Data | Filter | AutoFilter.
    You can now use the arrows that appear next to the column headings to set a filter.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter (2003)

    Auto filter is a little limited when it comes to multiple criteria. You should be able to do this using advanced filter in excel. Would it be possible to attach a small dummy file of records that can be used to illustrate how to do this?
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter (2003)

    Attached is an example of what I need.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter (2003)

    AutoFilter will do nicely here. First, click in the table and select Data | Filter | AutoFilter.
    For example, to view FA Members only, select (Non-Blanks) from the dropdown list in the FA Members column heading. (The dropdown arrow will become blue to indicate that you filtered on this column)
    To view clients who are both FA Members and Dental members, also select (Non-Blanks) from the dropdown list in the Dental Members column heading.
    To view all records again, select Data | Filter | Show All.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter (2003)

    That works and I will use that if the following isn't possible. Can there be a column with an if statement using the different scenarios. If Dent mem is null, if vis mem is null, "FAONLY". etc. I don't know if this is possible or practical but what they want to do is a pivot table using that field. The names in that field would be FAOnly, FADent, FADentVis, DentOnly, DentVis, VisOnly, FAVis. Thanks for your help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter (2003)

    Create a new column - in your sample worksheet it would be column E.
    In E1, enter a description such as Membership.
    In E2, enter the formula
    <code>
    =IF(B2="",IF(C2="",IF(D2="","None","VisionOnly"),I F(D2="","DentOnly","DentVis")),IF(C2="",IF(D2=""," FaOnly","FaVis"),IF(D2="","FaDent","FaDentVis")))
    </code>
    and fill down as far as needed. You can use this column in the AutoFilter or in a pivot table.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter (2003)

    Looks like what they are looking for - my manager wants to hire you. Thanks for all your help.

Posting Permissions

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