 |
 | 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 / March 2008ISNA match function help
Ok, i need some help. I have two columns of data. For one of the columns, i need to find out if its contained somewhere, anywhere in the other column. So how was going to do it was useing an ISNA, Match function and create a new column called Missing?, and then drag the code down, if its not contained then it will output True, if it is there, then it will output False. This usually works great. The only problem im having is that my column contents are kinda wacky. Here are some examples M4758375 12-67-8 056-76-23 08-22-456 So both columns have data like this. And for some reason, my ISNA code isnt working. Any ideas? Thanks, -Adam Can you give examples of items where the formula fails? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Ok, i need some help. > [quoted text clipped - 22 lines] > > -Adam The formula Fails on those types of Numbers i posted above M4758375 12-67-8 056-76-23 08-22-456 So what i mean is that these types of numbers are found in both columns, but the code still comes out as true. I have tested the code for very simple inputs such as 1, and 2, and it works fine. > Ok, i need some help. > [quoted text clipped - 19 lines] > > -Adam I can't get those values to fail.....Perhaps in one of the columns there are leading or trailing spaces, where the other column does not have them. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > The formula Fails on those types of Numbers i posted above > [quoted text clipped - 35 lines] >> >> -Adam Ron, Your right. heres 10 spaces at the end of these types of Data. Is there anyone, via a macro or code, to remove these 10 spaces from group of rows? > I can't get those values to fail.....Perhaps in one of the columns there are > leading or trailing spaces, where the other column does not have them. [quoted text clipped - 47 lines] > >> > >> -Adam If your data is in A1, put this formula in a helper column: =TRIM(A1) Copy the formula across if you also have data in column B, C, D etc, and copy down as necessary. Then highlight all the cells with this formula in, click <copy>, then click Edit | Paste Special | Values (check) | OK then <Esc>. This will have converted all the formulae to values, and any leading / trailing or multiple spaces will have been removed. You can then copy these values to overwrite the values that were in coumns A, B, C etc, and then you can delete the helper columns. Hope this helps. Pete > Ron, > [quoted text clipped - 54 lines] > > - Show quoted text - If every cell in the list has exactly 10 spaces after the text.... try this: Select the list, then... From the Excel Main Menu: <edit><replace> Find what: (enter 10 spaces here) Replace with: (leave this blank) Click [Replace All] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Ron, > [quoted text clipped - 61 lines] >> >> >> >> -Adam If the data is all like shown in the OP's examples, namely, with no leading or internal spaces, then he can put a single space in the "Find what:" field (and then follow the rest of your instructions) and eliminate all the spaces (no matter what their number). Rick > If every cell in the list has exactly 10 spaces after the text.... > try this: [quoted text clipped - 82 lines] >>> >> >>> >> -Adam These are all text values, and so you might have space(s) at the end of them and not know it - if you are looking for an exact match then you will not get it if one value has an extra space. Apply the TRIM function to both sets of data to see if this improves things. Hope this helps. Pete > The formula Fails on those types of Numbers i posted above > [quoted text clipped - 32 lines] > > - Show quoted text - Adam, Have you thought of using Data>Filter>Advanced? One column would be your data range and the other column would be your criteria range. Just make sure they both have the same heading. This will find any exact matches. HTH Jim > Ok, i need some help. > [quoted text clipped - 19 lines] > > -Adam |
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
|
©2008 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.