Avatar of Pau Lo
Pau Lo asked on

auto-filter for a large number of values

1a) What is the best way in excel to  auto/advanced filter a column of data for a huge list of values, to meet the "does not contain" criteria. Auto filter only seems to to give you 2 fields to enter values using its and/or options in auto filter. But I need to filter a sheet on a specific column of 100,000 records for all records  that 'do not contain' approximately 100 values in a specific column. 


1b) If the 'does not contain' values contains multiple words, e.g. "does not contain", do you need the list of values in quotation marks?


2) Secondly, is there any way to apply a filter to a list using 'does not contain' type logic, but exempt certain records from the filter which are on an exempt list (e.g. 'keep them in place' and don't filter them out.. such as records that do actually contain the keyword that you would be using in the 'does not contain' query. Is this at all practical with the features available somewhere on the data tab? 


I've been testing some of the various options in auto/advanced filter and I think I may be pushing the features to far with what I need to accomplish so hopefully there are alternatives that I am not aware of.

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

What kind of text do your cells contain?

What do your terms look like?
ASKER
Pau Lo

all text in the cells, they are actually address fields. so the does not contain are addresses.
aikimark

Please post a representative sample workbook with both the address fields, the 100 terms, and the criteria for excluding rows from filtering.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
byundt

If you have a different list of words in column F that you do not wish to exclude, your auxiliary formula can be used twice: once to include and then a second time to exclude. For the values to keep, the first formula should return 0 while the second returns FALSE.
=IF(SUMPRODUCT(COUNTIF(A2,"*" & F$2:F$400 & "*"))>0,0,SUMPRODUCT(COUNTIF(A2,"*" & G$2:G$400 & "*")))

=IF(OR(ISNUMBER(SEARCH(F$2:F$400,A2))),FALSE,OR(ISNUMBER(SEARCH(G$2:G$400,A2)))))

Open in new window