Can I use a formula to filter data?

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
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


"Josh Craig" <[email protected]> wrote in message
news:[email protected]...
> 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!

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Josh Craig" wrote:
> 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!

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.

"CLR" wrote:

> 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
>
>

"Josh Craig" wrote:
> ... 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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote:

> 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)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Josh Craig" wrote:
> > 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!

"Josh Craig" wrote:
> .. 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

> "Josh Craig" wrote:
> > .. 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
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

"Josh Craig" wrote:
> Oh yeah, of course. Should have worked that out myself.

No prob, Josh <g>.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---