Can I use a formula to filter data?

[Josh Craig]

Hi I just wanted to know if there was a formula I could use so data in column
A could be replicated in column B except without certain values from column A
which contain certain bits of text.

For example, if column A text contained the word "dog" I would want it to
show in column B but if it contained the word "cat" I wouldn't. But I don't
want blank spaces in column B next to the "cat" cells, I want the data to
move up so column B only contains "dog" cells with no spaces in between.

So the columns would be like this:

Column A Column B
Black Cat Black Dog
Brown Cat Black Dog
Black Dog Brown Dog
Yellow Cat
Black Dog
Brown Dog


Is this possible? Thanks in advance for your help!

 

[CLR]

For what you're asking, a formula alone is insufficient. VBA would be
required. But before you get into that, you might take a look at the
Autofilter feature. First select a cell in column A, then do Data > Filter

Autofilter > Custom > Contains > Dog..............this procedure will

filter out all the cells that do not contain the string "Dog", the only
difference being is that the results would remain in column A rather than
transferring over to column B, if that's ok............when you're finished,
do Data > Filter > AutoFilter again and all will return to normal..........

Vaya con Dios,
Chuck, CABGx3

 

[Max]

One play using non-array formulas ..
Assuming source data in A1 down

Put in B1:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1:
=IF(ISNUMBER(SEARCH("Dog",A1)),ROW(),"")

Select B1:C1, fill down to last row of data in col A
Col B will return the required results, all neatly bunched at the top

Replace SEARCH with FIND in the criteria col C if you need it to be case
sensitive (SEARCH is not case sensitive)

 

[Josh Craig]

Sorry Chuck, a basic filter isn't sufficient. There's definitely no way this
can be done? It really doesn't seem like a particularly complicated thing to
do.

 

[Max]

... a basic filter isn't sufficient.
There's definitely no way this can be done?

Try the play using non-array formulas in the other response to your post ..

 

[Josh Craig]

Pure genius, Max!

One other thing though, can I modify that formula to give me results in
column B that show all from column A NOT containing "dog"?

 

[Max]

.. One other thing though, can I modify that formula to give me results in
column B that show all from column A NOT containing "dog"?

Just slightly adjust* the criteria formula in C1 to:
=IF(ISNUMBER(SEARCH("Dog",A1)),"",ROW())
then copy C1 down

[swap the IF_TRUE/IF_FALSE values around]

(no change to the formulas in col B)

 

[Josh Craig]

Oh yeah, of course. Should have worked that out myself.

.. One other thing though, can I modify that formula to give me results in
column B that show all from column A NOT containing "dog"?

Just slightly adjust* the criteria formula in C1 to:
=IF(ISNUMBER(SEARCH("Dog",A1)),"",ROW())
then copy C1 down

[swap the IF_TRUE/IF_FALSE values around]

(no change to the formulas in col B)

 

[Max]

Oh yeah, of course. Should have worked that out myself.

No prob, Josh <g>.