 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Can 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 > 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 [quoted text clipped - 16 lines] > > 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. > 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 [quoted text clipped - 7 lines] > Vaya con Dios, > Chuck, CABGx3 > ... 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 ..  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 16 lines] > > Is this possible? Thanks in advance for your help! 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"? > One play using non-array formulas .. > Assuming source data in A1 down [quoted text clipped - 30 lines] > > > > Is this possible? Thanks in advance for your help! > .. 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) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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"? [quoted text clipped - 6 lines] > > (no change to the formulas in col B) > Oh yeah, of course. Should have worked that out myself. No prob, Josh <g>. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.