Sorting/Filtering by Specific Information

**Luna1** · 12-22-2011, 10:58 AM

I need help creating an excel spreadsheet where I can have a list of all of my clients and a list of all of the colleges they have applied to and be able to filter by different variables. For example, not only do I need to be able to see the name of the client and all of the colleges they applied to, but I need to also be able to sort by a specific college and see all the clients that have applied to that college. Any help would be greatly appreciated, thank you!

**tigeravatar** · 12-22-2011, 12:13 PM

Luna1,

Here's an exammple of how it should be laid out to get the functionality you want:

Please Login or Register  to view this content.

**Luna1** · 12-22-2011, 12:31 PM

Hi tigeravatar,

Thank you for the post. Once I set up my clients and colleges as you have described, how can I easily extract which clients have applied to a particular college? The list of colleges is going to be quite expensive..is there a certain formula I can use?

**tigeravatar** · 12-22-2011, 12:34 PM

You would use Excel's autofilter. Here's some links for more information:

http://www.contextures.com/xlautofilter01.html
http://office.microsoft.com/en-us/ex...010073941.aspx
http://office.microsoft.com/en-us/ex...010082314.aspx

**Luna1** · 12-22-2011, 12:44 PM

Ok, great thank you.

One last thing, is there a way to assign multiple colleges to one client without having to list the clients name more than once? It will be difficult to easily see a count of how many clients there are when their name is listed more than once.

**tigeravatar** · 12-22-2011, 12:49 PM

Because A1 is a header, the range starts on A2. Adjust the ending cell to suit. You can use this formula to get the number of unique, non-blank entries in the range:
=SUMPRODUCT(--(A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))