Can I use a formula to filter data?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
Click to expand...
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
 
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)
 
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.
 
Josh Craig said:
... a basic filter isn't sufficient.
There's definitely no way this can be done?
Click to expand...

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

---
 
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"?
 
Josh Craig said:
.. 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"?
Click to expand...

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)

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

Max said:
Josh Craig said:
.. 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"?
Click to expand...

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)

---
Click to expand...
 
Back
Top