Filtering using a List

jakeman

Active Member
Joined
Apr 29, 2008
Messages
325
Office Version
  1. 365
Platform
  1. Windows
Is it possible to filter a bunch of records for agents based upon a small list of names?

For example, my data set is huge...well over a thousand rows of data. There is a field called "Agent" in my list and out of those agents, I have a list of about 6-7 agents that I want to filter on whenever I need to. There are well over a hundred agents so having to select just a handful of people whenever I need to see data specific to them is cumbersome.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
In your autofilter code you can use an array for the criteria like...

Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues

And I believe that array could be cell references

edit: this will work for excel 2007
 
Last edited:
Upvote 0
One way, you could add a new field (column) and call it Agent2 or whatever and only include those 6-7 agents. Then filter that column for just those, so you won't have to scroll through all the other agents.
 
Upvote 0
You can always set up a retrieval table to perform a vlookup for those agents you want to view.

Other than that, you can always use the Filter function that is probably sitting on your toolbar under DATA. If you have Excel 2007 or newer, you can filter on multiple selections. If you have Excel 2003 or earlier, you are limited to one choice...unfortunately.
 
Upvote 0
Also: Extend the data area with a column where you mark each agent record if the agent is in your target list...

For example:

N1: InSubList

N2, just enter and copy down:

=ISNUMBER(MATCH(A2,AgentSubList,0))+0

where AgentSubList is the name of the range housing the relevant subset of agents and A2 is the first cell under the Agent field in the data area. After this step, you can autofilter the data area on InSubList set on 1 at any time.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,738
Members
453,616
Latest member
nathancook

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