Excel Advanced Filter Question

[ab123456]

I am trying to use the advanced filter in excel to extract some data from a large file. My question is how do I filter a column so it shows only data that does not contain a particular text string?

I know this can be done realtively simply using the AutoFilter function but this is only a part of a more complex filter that is beyond what the autofilter can do.

Thanks.

 

[electricpete]

It's not the most elegant solution but I would create a new column to test for the condition and use autofilter to select appropriate rows based on contents of that column.

For instance new column with contents in H2
=NOT(ISERR(FIND("SEARCHSTRING",A2)))
where A is the column you're testing content on. Copy the contents down column H. Setup autofilter to include the block columns A through H. Use autofilter to examine True or False results for column H. False does not contain the text.

For a little more flexibility, instead of "searchstring" you can refer to a named variable stored elsewhere in the sheet and then if you want to filter on other strings, just change that cell and refilter.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[ab123456]

Thanks for that electricpete.

I did eventually manage to get the syntax right for the advanced filter which is a bit neater. In the filter criteria i needed ="<>string"

 

[MintJulep]

Please post your complete solution so others may learn from your experience.

 

[electricpete]

ab123456 - you still created a new column, right?

If so, the difference is that my solution would discriminate rows where the target cell does/does not contain the search string, while yours would discriminate rows where the target cell does/does/not equal the search string.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[ab123456]

Thanks for all showing an interest in my problem, here are a few more details about what i was trying to achieve and how i eventually solved it.

I have a text file containing a list of major equipment on our site, the file contains a tag no for each bit of kit and approx 10 other fields with details such as equipment location, type of location etc. I opened the text file in excel such that each piece of equipment was stored on a new row. approx 8000 rows long x 10 columns.

My Problem. I want to filter such that i see only items from equipment locations A or B but i dont want to see any of equipment type C. The fact that i am interested in location A or B means that the autofilter couldnt accomplish this task without running two separate filters.

Solution. Using the Advanced filter.
I took me a while to get the syntax right for this and that was the purpose of my orignal question. To find equipment in location A that is not of type C I enter the following in the first row of the criteria range (that is the blank rows above my data i wish to filter) ...

In the location cloumn ="=LocationA" and on the same row in the type location ="<>TypeC"
and on the row immediately below I enter ="=LocationB" in the location col and ="<>TypeC" in the type column

Electricpete is correct when he states that my search will only remove data where the equipment type exactly matches TypeC and not merely contain this text, this can be overcome by the use of wildcard character, so if i wanted to remove all equipment where the equipment field contains the text TypeC I could use ="<>$TypeC$"

Thanks again for all showing an interest and i hope this exlpanation is of some use to others trying use the advanced filter facility

 

[electricpete]

Thanks. I was thinking auto-filter, not advanced filter.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[EGT01]

ab123456,

I'm curious now, I'm not familiar with using $ as a wildcard character. In Excel 2000, I thought only *,?,~ were wildcard characters. Have I overlooked this or are you using a different version of Excel?

By the way, while performing a recent search I found this site that seemed to have some interesting info about advanced filters.

http://www.contextures.com/xladvfilter01.html

 

[Latexman]

ab123456,

What was the problem with running two filters under Autofilter? That seems to be much quicker than figuring out the code for what you did?

Good luck,
Latexman

 

[bpeirson]

Latexman,

I see great value in finding alternate solutions to what seem like easy problems. The alternate solution can have unexpected uses.

Two years ago I started recording macros in Excel, I then started to tweek the code to add flexibility to my macros.

Most of what I did could have been done without VBA but I like alternatives. All this has led to my ability to use Excel as a file management program.

I have opened CNC g-code with Excel based VBA to search for and replace various text strings to improve or totally change the original g-code. Similar things can be done to any file which can be opened by Excel. Once I learned an alternate solution to some of my early problems, new applications for the solution became apparent.