 |
 | 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 / New Users / February 2008Matching partial portions of cell contents
I have 3 columns of alphanumeric data. The first represents a list of partial license numbers with only numeric data. The 2nd and 3rd represent complete license numbers with alphanumeric data. I want to find matches for as many digits of the partial license numbers (in the 1st column) as possible with the complete license numbers in the 2nd and 3rd columns. Is there a way to do this in Excel? If I understand what you're after, I would think that you'd want to separate the 2 columns containing the complete plate numbers, so that you could individually mark *each cell* in those 2 columns, where a match was found with the partial numbers in the first column. Or am I on the wrong track? Say you have 50 rows of partials in Column A, from A1 to A50, and 2 columns, 100 rows each, with complete numbers, from B1 to C100. Insert a new column between B & C, making the second column of complete numbers the NEW Column D. In the NEW, blank Column C, enter this in C1: =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$50,B1)))) And copy down to C100. In E1 enter: =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$50,D1)))) And copy down to E100. This should give you the number of hits (matches) for each complete plate number against the list of partials in Column A.  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > I have 3 columns of alphanumeric data. The first represents a list of > partial license numbers with only numeric data. The 2nd and 3rd > represent complete license numbers with alphanumeric data. I want to > find matches for as many digits of the partial license numbers (in the > 1st column) as possible with the complete license numbers in the 2nd > and 3rd columns. Is there a way to do this in Excel? I tried this but I'm not sure I did it right or how to interpret what came up. You can see the actual file by going to http://www10.sendthisfile.com/d.jsp?t=tCJpkJBOJM3elTd0lGjdKo9a. Column A has the incomplete license (they're not license plate numbers, by the way) numbers, and Columns B and C have complete numbers. I'm looking to get as close as possible to a match--say at least 4 digits from the numbers in Col A in the same order in Cols B or C. > If I understand what you're after, I would think that you'd want to separate > the 2 columns containing the complete plate numbers, so that you could [quoted text clipped - 38 lines] > > 1st column) as possible with the complete license numbers in the 2nd > > and 3rd columns. Is there a way to do this in Excel? Follow my directions exactly as I stated them in my first post. Change the formula to make up for the actual range of partials to: =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$1258,B28)))) And enter in the NEW C1. Enter this in E1: =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$1258,D28)))) You can now *double* click on the fill handle of C1 and E1 respectively, And the formula will *automatically* be copied down the columns as far as there is data in the adjoining columns. A 0 in Columns C and E mean there is no match in B and/or D.. If you get a return of 1 or 2, that means the complete license number has a number series that is contained (matched) in Column A, in either 1 or 2 instances. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >I tried this but I'm not sure I did it right or how to interpret what > came up. You can see the actual file by going to [quoted text clipped - 50 lines] >> > 1st column) as possible with the complete license numbers in the 2nd >> > and 3rd columns. Is there a way to do this in Excel? Ah, thanks. Now, having identified the fact that there is a match somewhere in Col A, is there a way to flag, e.g., change font color or format, those cells where the match has been found? > Follow my directions exactly as I stated them in my first post. > [quoted text clipped - 81 lines] > >> > 1st column) as possible with the complete license numbers in the 2nd > >> > and 3rd columns. Is there a way to do this in Excel? How about returning just the *row number* of the first match? Try this *array* formula in C1: =IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,B1))))=0,"", MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,B1)),0)) SignatureArray formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *AFTER* the CSE entry, copy down to C33. Use this *array* formula in E1, and after the CSE entry, copy down to E116: =IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,D1))))=0,"", MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,D1)),0)) This formula will either return a blank cell if no match is found, or the *row number* of the first match in Column A. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Ah, thanks. Now, having identified the fact that there is a match somewhere in Col A, is there a way to flag, e.g., change font color or format, those cells where the match has been found? On Feb 18, 12:45 pm, "RagDyer" <ragd...@cutoutmsn.com> wrote: > Follow my directions exactly as I stated them in my first post. > [quoted text clipped - 92 lines] > >> > 1st column) as possible with the complete license numbers in the 2nd > >> > and 3rd columns. Is there a way to do this in Excel? > How about returning just the *row number* of the first match? > [quoted text clipped - 132 lines] > > - Show quoted text - That's perfect! Thanks much! You're welcome, and as far as your feed-back ... WHY did you ruin <bg> a good relationship by bottom posting that feed-back when all the rest of your posts were properly top posted? Just kidding(maybe?). SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- <snip> > - Show quoted text - That's perfect! Thanks much! Just in a hurry, I guess. Mea culpa. > You're welcome, and as far as your feed-back ... WHY did you ruin <bg> a > good relationship by bottom posting that feed-back when all the rest of your [quoted text clipped - 14 lines] > > That's perfect! Thanks much! |
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.