+ Reply to Thread
Results 1 to 9 of 9

Can I use a formula to filter data?

  1. #1
    Josh Craig
    Guest

    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!

  2. #2
    CLR
    Guest

    Re: Can I use a formula to filter data?

    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" <JoshCraig@discussions.microsoft.com> wrote in message
    news:E1A85AFF-FE41-4F34-B4C4-E0C7E6D73D2E@microsoft.com...
    > 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!




  3. #3
    Max
    Guest

    Re: Can I use a formula to filter data?

    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!


  4. #4
    Josh Craig
    Guest

    Re: Can I use a formula to filter data?

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


  5. #5
    Max
    Guest

    Re: Can I use a formula to filter data?

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

  6. #6
    Josh Craig
    Guest

    Re: Can I use a formula to filter data?

    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!


  7. #7
    Max
    Guest

    Re: Can I use a formula to filter data?

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

  8. #8
    Josh Craig
    Guest

    Re: Can I use a formula to filter data?

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


  9. #9
    Max
    Guest

    Re: Can I use a formula to filter data?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1