Auto Filter for Clients in Multiple Categories

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Auto Filter for Clients in Multiple Categories

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto Filter for Clients in Multiple Categories

     
    Hello everyone,

    My first post here- I have seacrched through the archives without success- am hoping someone can help with this basic query.

    I have set up a fairly standard table with Company name, contact, details, website etc. and I need a column for industry classification. Using auto Filters, this works perfectly when each company only has one industry classification, but I cant figure out how to do this for multiple classifications. That is , I cant work out how to enter the data, nor can I work out how to make it filter for me.

    For example, Company A might be classified as a General Insurer, Car Insurer, Boat insurer, Special Items Insurer etc. I need to be able to search by Industry class so I can pull down General Insurer , for example, and see all the companies that fit this category, or perhaps just search for Compnay A to see all the types of insurance it offers.

    Any suggestions would be greatly appreciated.
    Thank you

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Hi Remix:

    Welcome to MrExcel Board!

    In the following illustration, I have used Advanced Filter ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y040201h1a.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    source IndClassifGeneral
    2
    criteria General
    3
    copyto
    4
    5
    CompanyNameContactdetailswebsiteIndClassif CompanyNameContactdetailswebsiteIndClassif
    6
    Ab_1c_2d_2Boat Bb_2c_3d_3General
    7
    Bb_2c_3d_3General Fb_6c_7d_7General
    8
    Cb_3c_4d_4Car
    9
    Db_4c_5d_5Boat
    10
    Eb_5c_6d_6Home
    11
    Fb_6c_7d_7General
    Sheet18

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Is this what you are looking for? If you need to discuss this further, please post back!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Feb 2004
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Hello again,

    Thank you for your prompt reply....the table you have posted is similar to mine, but the problem exists where one compnay falls into multiple categories.

    Using your example....Say Company A does Boat, Car, and Home ....Company b does General and Car etc etc...this is where I have troubles, ie where each insurer may be classified as more than one type. I hope I have been more clear here.
    Thanks again

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Quote Originally Posted by Remix
    Hello again,

    Thank you for your prompt reply....the table you have posted is similar to mine, but the problem exists where one compnay falls into multiple categories.

    Using your example....Say Company A does Boat, Car, and Home ....Company b does General and Car etc etc...this is where I have troubles, ie where each insurer may be classified as more than one type. I hope I have been more clear here.
    Thanks again
    Hi Remix:

    You did not say what is the trouble? In the context of working with EXCEL, if a company does multiple insurances -- so what is the problem? What are you trying to find? Can you describe clearly what are you having trouble with -- or narrate in plain english what would you like to do -- and let us not make any assumptions about whether something can be done or not!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Aug 2003
    Location
    Melbourne
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Hi Remix,

    I am not very advanced at Excel compared with the gurus on this site!! Having said that, this is how I would deal with this problem - perhaps one of the gurus can expand on this?

    I would insert another column in the table. Then I would have a cell at the top of the inserted column (near your table headings) - say, cell G1 - where you could input the industry type, say, "Boat Insurance". In the inserted column, I would copy the following formula against all the records in your table:

    =IF(ISERROR(FIND($G$1,B31)),"No","Yes")

    This would then put a "Yes" in the inserted column against any companies that offered Boat Insurance and a "No" if they don't. You could then select "Yes" in the data filter.

    If you wanted to get really fancy, you could use a drop-down box for the user to select an industry type, and lookup tables to get the find value (the equivalent of "$G$1" in the above formula) so that you don't have any problems if someone spells the industry category incorrectly.

    Good luck, let us know if this works for you.

    Regards,
    Jane

  6. #6
    New Member
    Join Date
    Feb 2004
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    and let us not make any assumptions about whether something can be done or not! im not sure thats happening, thanks for your input.

    My specific problem is that if a company has more than one type of insurance, eg, car , boat, general etc , i need to be able to sort through the list of companies.

    Eg. company A does car insurance, and Company B does , car, boat and health. If I need to search for compnaies that do car insurance, i only get those like company A (where Car is the only type listed) Ideally, if i want to search for companies doing car insurance, both company A and compnay B should show up, regardless of any other insurance types they do. I hope this makes sense.

    Also, I cannot keep adding additional colums for each type of insurer ( some compnaies may have up to 10 types of insurance)

    Also , i should add that this spreadsheet is ultimately for a client of mine who has limited skills; she just needs the template to enter data into, then be able to access data easily.


    Thank you again

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,436
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Quote Originally Posted by Remix
    ...
    My specific problem is that if a company has more than one type of insurance, eg, car , boat, general etc , i need to be able to sort through the list of companies.

    Eg. company A does car insurance, and Company B does , car, boat and health. If I need to search for compnaies that do car insurance, i only get those like company A (where Car is the only type listed) Ideally, if i want to search for companies doing car insurance, both company A and compnay B should show up, regardless of any other insurance types they do. I hope this makes sense.

    ...
    The Company B does Car, Boat, and Health. How is all this recorded? Like:

    Company_B Car
    Company_B Boat
    Company_B Health

    Or just:

    Company_B Car,Boat,Health

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

    Quote Originally Posted by Remix
    ....
    Eg. company A does car insurance, and Company B does , car, boat and health. If I need to search for compnaies that do car insurance, i only get those like company A (where Car is the only type listed) Ideally, if i want to search for companies doing car insurance, both company A and compnay B should show up, regardless of any other insurance types they do. I hope this makes sense.
    Hi Remix:

    So it appears your data looks like in ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y040202h1a.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    5
    CompanyNameContactdetailswebsiteIndClassif
    6
    Ab_1c_2d_2Car
    7
    Bb_2c_3d_3Car,Boat,Health
    8
    Cb_3c_4d_4General
    9
    Db_4c_5d_5Boat
    10
    Eb_5c_6d_6Health
    11
    Fb_6c_7d_7General,Car
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    If such is the case then you can use AutoFilter -- using IndClassif (or whatever the field is named in your list), use Custom, then Contains, and then the type of insurance, e.g. car to filter the list.

    Using Advanced Filter will give you greater options and greater flexinility, but using AutoFilter should do the job.

    If your data is laid out differently, please post the data using HTMLmaker so we can see what you are working with.
    ....
    Also , i should add that this spreadsheet is ultimately for a client of mine who has limited skills; she just needs the template to enter data into, then be able to access data easily.
    ....
    So, using AutoFilter on IndClassif field -- Custom -- Contains ... should do the job. Good Luck!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    New Member
    Join Date
    Feb 2004
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter for Clients in Multiple Categories

      
    Thanks Yogi,

    Works beautifully!!

    Cheers

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com