Auto Filter for Clients in Multiple Categories

Remix

New Member
Joined
Feb 1, 2004
Messages
4
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi Remix:

Welcome to MrExcel Board!

In the following illustration, I have used Advanced Filter ...
y040201h1a.xls
ABCDEFGHIJK
1sourceIndClassifGeneral
2criteriaGeneral
3copyto
4
5CompanyNameContactdetailswebsiteIndClassifCompanyNameContactdetailswebsiteIndClassif
6Ab_1c_2d_2BoatBb_2c_3d_3General
7Bb_2c_3d_3GeneralFb_6c_7d_7General
8Cb_3c_4d_4Car
9Db_4c_5d_5Boat
10Eb_5c_6d_6Home
11Fb_6c_7d_7General
Sheet18


Is this what you are looking for? If you need to discuss this further, please post back!
 
Upvote 0
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 :)
 
Upvote 0
Remix said:
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!
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
Remix said:
...
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
 
Upvote 0
Remix said:
....
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 ...
y040202h1a.xls
ABCDE
5CompanyNameContactdetailswebsiteIndClassif
6Ab_1c_2d_2Car
7Bb_2c_3d_3Car,Boat,Health
8Cb_3c_4d_4General
9Db_4c_5d_5Boat
10Eb_5c_6d_6Health
11Fb_6c_7d_7General,Car
Sheet1


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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top