Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Data Search | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Pivot Table Data Filter Problem | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions |