Comparing text strings

I have two columns populated with names and I need to compare them for 
duplicates.  Some duplicate names may contain middle initial or middle names 
and some don't. So they may not be EXACT.  Please give me suggestions on 
formulas or funcions that would help me accomplish this task.
Thanks.
0
Utf
11/27/2009 7:12:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
879 Views

Similar Articles

[PageSpeed] 52

On Fri, 27 Nov 2009 11:12:01 -0800, Comparing columns with text <Comparing
columns with text@discussions.microsoft.com> wrote:

>I have two columns populated with names and I need to compare them for 
>duplicates.  Some duplicate names may contain middle initial or middle names 
>and some don't. So they may not be EXACT.  Please give me suggestions on 
>formulas or funcions that would help me accomplish this task.
>Thanks.

This is an interesting problem, and not particularly easy to solve, unless you
can be very specific, and limiting, in the allowable variability.

For example, given the following:

Name = FirstName [MI or Middle Name] LastName

you could construct an algorithm that looks first for an exact match in
FirstName and LastName followed by a comparison of what is in between.

For example:

Name 1		matches in Name 2
  No MI of MN	  anything
  MI		  same MI or MN starting with MI or nothing
  MN		  MI = left(MN,1) or same MN or nothing 	


Exactly how to construct this algorithm depends critically on how your data is
stored, and how you define "duplicate names"

On the other hand, if you are looking to answer the question, "do two different
names represent the same person", then a Soundex (or NYSIIS) method might be
better.

http://j-walk.com/ss/excel/tips/tip77.htm
http://en.wikipedia.org/wiki/New_York_State_Identification_and_Intelligence_System
--ron
0
Ron
11/27/2009 8:07:25 PM
really interestgin

one way is to
trim all names and  sort colums and compare first few letters then
compare last names (last name with mid() function).

yours
sajay




"Comparing columns with text" <Comparing columns with 
text@discussions.microsoft.com> wrote in message 
news:631049AB-8D09-4C42-BCEE-51FA39C7BB65@microsoft.com...
> I have two columns populated with names and I need to compare them for
> duplicates.  Some duplicate names may contain middle initial or middle 
> names
> and some don't. So they may not be EXACT.  Please give me suggestions on
> formulas or funcions that would help me accomplish this task.
> Thanks. 

0
sajay
11/28/2009 6:46:48 AM
Maybe this formula?

=AND(LEFT(A1,FIND(" ",A1)-1)=LEFT(B1,FIND(" 
",B1)-1),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" 
",99)),99))=TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" 
",99)),99)),IF(LEN(A1)-LEN(SUBSTITUTE(A1," 
",""))=LEN(B1)-LEN(SUBSTITUTE(B1," ","")),MID(A1,FIND(" ",A1&" ")+1,FIND(" 
",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)=MID(B1,FIND(" ",B1&" 
")+1,FIND(" ",B1&" ",FIND(" ",B1&" ")+1)-FIND(" ",B1&" ")-1),TRUE))

-- 
Rick (MVP - Excel)


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:rhb0h5h2rdun41eo19uttd0dov3iihffbg@4ax.com...
> On Fri, 27 Nov 2009 11:12:01 -0800, Comparing columns with text <Comparing
> columns with text@discussions.microsoft.com> wrote:
>
>>I have two columns populated with names and I need to compare them for
>>duplicates.  Some duplicate names may contain middle initial or middle 
>>names
>>and some don't. So they may not be EXACT.  Please give me suggestions on
>>formulas or funcions that would help me accomplish this task.
>>Thanks.
>
> This is an interesting problem, and not particularly easy to solve, unless 
> you
> can be very specific, and limiting, in the allowable variability.
>
> For example, given the following:
>
> Name = FirstName [MI or Middle Name] LastName
>
> you could construct an algorithm that looks first for an exact match in
> FirstName and LastName followed by a comparison of what is in between.
>
> For example:
>
> Name 1 matches in Name 2
>  No MI of MN   anything
>  MI   same MI or MN starting with MI or nothing
>  MN   MI = left(MN,1) or same MN or nothing
>
>
> Exactly how to construct this algorithm depends critically on how your 
> data is
> stored, and how you define "duplicate names"
>
> On the other hand, if you are looking to answer the question, "do two 
> different
> names represent the same person", then a Soundex (or NYSIIS) method might 
> be
> better.
>
> http://j-walk.com/ss/excel/tips/tip77.htm
> http://en.wikipedia.org/wiki/New_York_State_Identification_and_Intelligence_System
> --ron 

0
Rick
11/28/2009 6:25:39 PM
On Sat, 28 Nov 2009 13:25:39 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>Maybe this formula?
>
>=AND(LEFT(A1,FIND(" ",A1)-1)=LEFT(B1,FIND(" 
>",B1)-1),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" 
>",99)),99))=TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" 
>",99)),99)),IF(LEN(A1)-LEN(SUBSTITUTE(A1," 
>",""))=LEN(B1)-LEN(SUBSTITUTE(B1," ","")),MID(A1,FIND(" ",A1&" ")+1,FIND(" 
>",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)=MID(B1,FIND(" ",B1&" 
>")+1,FIND(" ",B1&" ",FIND(" ",B1&" ")+1)-FIND(" ",B1&" ")-1),TRUE))
>
>-- 
>Rick (MVP - Excel)

Maybe, but we don't have enough information yet.

One thought, based on only on looking quickly at your formula, is that it only
compares A1 & B1.  My guess would be that we need to compare all of column B
with A1.
--ron
0
Ron
11/28/2009 7:02:18 PM
>>Maybe this formula?
>>
>>=AND(LEFT(A1,FIND(" ",A1)-1)=LEFT(B1,FIND("
>>",B1)-1),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
>>",99)),99))=TRIM(RIGHT(SUBSTITUTE(B1," ",REPT("
>>",99)),99)),IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
>>",""))=LEN(B1)-LEN(SUBSTITUTE(B1," ","")),MID(A1,FIND(" ",A1&" ")+1,FIND("
>>",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)=MID(B1,FIND(" ",B1&"
>>")+1,FIND(" ",B1&" ",FIND(" ",B1&" ")+1)-FIND(" ",B1&" ")-1),TRUE))
>>
>>-- 
>>Rick (MVP - Excel)
>
> Maybe, but we don't have enough information yet.
>
> One thought, based on only on looking quickly at your formula, is that it 
> only
> compares A1 & B1.  My guess would be that we need to compare all of column 
> B
> with A1.

Ahh, yes, you might be right on that. That would probably require a macro 
then, I would guess.

-- 
Rick (MVP - Excel) 

0
Rick
11/28/2009 7:43:20 PM
Reply:

Similar Artilces:

Changing subject/conversation text color
I'm using Outlook 2003. When I customize my view and group by conversation, the subject text is light gray, which is hard to read. Is there any way to change this? Thanks in advance, JBL. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com ...

counting embedded text #2
If I format the information with "Valid" or "Not Valid" first followe by an explaination, it works. The other option is to separate th status from the explaination. IE: have a separate column called vali and put a yes or no and count those. I only have about 45 entries s far, so it isn't that big of deal. So far, using the wildcard seems to be working -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com...

text balloons
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How can I create text balloons with pointers for use in a 2000 year timeline with 20 events? Drawing Tools. HTH |:>) Bob Jones [MVP] Office:Mac On 5/18/10 7:00 AM, in article 59bb8a09.-1@webcrossing.JaKIaxP2ac0, "Dash@officeformac.com" <Dash@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel > How can I create text balloons with pointers for use in a 2000 year timeline > with 20 events? ...

Missing text in email
I am having a problem with Users complaining of email missing the text when it was there originally. Also some have gotten emails back from people saying what they sent was blank, then when they check their sent items... sure enough it is blank!! Running Exchange 2003 on Windows 2003 with latest Service Pac Clients are Outlook 2000 with Latest Patches, etc Changing settings as to how the emails are formatted changes nothing (text/rich text/HTML Any help would be...... helpfull What AV are you running? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion ...

OL2000: Reply to HTML messages in plain text
Outlook Express includes an option to "reply to messages using the format in which they were sent". Clearing this option will make OE reply in plain text regardless of the original message format. I generally do not send e-mail in HTML at all. Outlook makes me manually change the format to plain text every time I reply to a HTML message. How could I report this issue to Microsoft? Thanks, Ivan Ivan B�tora <xxx@xxx.xxx> wrote: > Outlook Express includes an option to "reply to messages using the > format in which they were sent". Clearing this option will ma...

REFORMAT A TEXT CELL
-- Jerry Arnone PMP, IT PROJECT+, CCNA, MCSE, CCA, CNA, A+, SECURITY+, MS Project White Belt JARNONE@BELLSOUTH.NET ...

Auto Update Text box
Hello, I'm building my first ever Access datebase and I have a form called Enter Comp. In this form I have a combo box called Combo9 that gets it's data from a table called CompType. In the Table CompType I have three fields. The Comp9 combo box gets it data for the second field in the table the field is called Compdescription. On the form Enter Comp I also have a text box called CompAmount. I want this text box to auto populate once a user choices a comptype from the Combo9 combo box with the number that is in the Points table on the CompType table. Currently on the C...

Compare Worksheets #2
Hey all, is there any way to compare two worksheets? The limitations in the worksheet are: 1. The cells in some columns are merged. 2. However, all the data, whether merged or not, the formated remains the same. I meant if a1 and a2 are merged in sheet A, the same a1 and a2 are merged in Sheet B. Please help if there is any way to compare the worksheet. Also am not good at script. Please specify if there is any method for that. Your quick response is appreciated. Thanks, Pravi :) -- praveen_khm ------------------------------------------------------------------------ praveen_khm'...

exporting to text file database using pipe delimiting
Does anyone know of a way to export a spreadsheet to a custom delimited text file? I want to export one to a pipe (|) delimited file, but i can't seem to find a way to do it. Please.... :) Take a look here: http://www.mcgimpsey.com/excel/textfiles.html In article <BEA4D065-B51F-4ABF-90DC-93A0F61944DA@microsoft.com>, "NickP" <anonymous@discussions.microsoft.com> wrote: > Does anyone know of a way to export a spreadsheet to a custom delimited text > file? > I want to export one to a pipe (|) delimited file, but i can't seem to find a > way to ...

Text file encodings?
We recently migrated from 5.5 to 2003. One of our employees sends a text file to a client. The text file has a number as the extension. sample.301 for example. Every day the numbered extension is different. Under 5.5 there was no issue. Since the migration, the client is complaining that the file is coming in encoded as base64. Is this a server setting somewhere? Thanks, Loren ...

White Text On Black
I've looked through my printer's settings (Epson) and haven't found anything helpful there. Is there a setting in Word 2004 that'll reverse the colors? No sense in running through all that black ink. Thanks. Dave I'm not sure exactly what you're asking, but my guess is that it's best answered by simply saying that there is no such thing as "white" ink - at least not at anything less than special spot color at the commercial printing level. White on the typical home/office printer simply means the absence of ink in those locations where it's called ...

Comparing input to numerous values
Howdy, Is there a simple way in VBA to compare input from the user to a set amount of constants? Example: Input box asks for a value, then I need to compare that value to A,B,C,D. If the input does not equal one of the four then an error msg pops up. Would rather not write four if then statements. Thanks in advance, Mjack -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5141 View this thread: http://www.excelforum.com/showthread.php?threadid=494017 Maybe like...

Mission Impossible
I've been given the project of comparing a current Excel Spreadsheet against a historical Spreadsheet. They both have the same Employee ID.( Or new ones added on the Current File ) My task is ( should I chose to accept - Mission Impossible ) Is to determine the following: * How many new records are added on the Current File * Identify any changes that occur on the either file This could possibly involves creating something called a - Changed File. I've used a program called ExelDiff, but it failed miserably , when the inform...

Multiple Text Colors in Same Cell
Hello -- I am wondering if there is a way to format a cell so that multiple colors can be assigned to different text within the cell. I am trying to construct a simple in-cell bar chart representing the distribution of data across different categories, and want to be able to distinguish across the different categories using colors. For instance, this would create a two category bar chart, but I have no way to distinguish between the first category and the second category: =REPT(A1,A2)&REPT(A1,B2) Is there any function (e.g. COLOR() ) that would be able to do the following: =COLOR(REP...

anchor graphic in column 1 to text in column 2 in frontpage
as above Float graphic right in column 1 and float text left in column 2? "cog" wrote: > as above "cog" <cog@discussions.microsoft.com> wrote in message news:DC361C24-771F-4C19-B48A-F86AE8421394@microsoft.com... > as above I wrote this years ago for fp98 http://accessfp.net/how-to.htm Don't know what your version is but still holds true I presume you mean bookmarks? btw best to put the question in the body of the page for all sorts of reasons. If you want to bookmark within a page. Say you want to make a 'bottom of page' and ...

Entering Text and Date
I need a macro that will enter some text "Uploaded" and the date and time in this format "The text" date time such as Uploaded 04/19/07 12:42 PM. I don't want this time to change after it is entered" Can any one help? Herschel , Sub EnterUpload() ActiveCell.Value = "Uploaded " & Format(Now, "mm/dd/yy h:mm AM/PM") End Sub HTH, Bernie MS Excel MVP "Herschel Lawhorn" <Herschel Lawhorn@discussions.microsoft.com> wrote in message news:79E4EA03-2757-45AA-BD52-6E2553483265@microsoft.com... >I need a macro that will enter...

create a formula by using text from other cells
I need to effectively use multiple If/Vlookup functions. i.e. if it's Oranges, Lookup price history in "oranges worksheet". But I have too many categories to search in so this is not possible. So I had another solution, but can't make it work... In this case I am using a VLOOKUP formula and want to use one cell to search on (a1) and another cell to tell it which spreadsheet to search in (Sheet2) i.e. =Vlookup(a1,'[WorkbookData.xlsx](CellA2 i.e.Worksheet Name),2,False) So is there a way to create a formula that uses text from another cell? INDIRECT is yo...

I want guidelines on top of images and text
I'm using Publisher 2003. Is there a way to show the guides on top of images and text boxes? I can see the part of the guide that is uncovered, but how am I supposed to see the part that is covered by images and text boxes? Thanks You can create lines on top of the guides and bring those to the front, just don't forget to delete them before you print. Keep your text boxes transparent. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "audball" <audball@discussions.microsoft.com> wrote in message news:B83E...

Seperating text if there's more than a one space between them
Hi all I need to seperate text in a column into different columns. Text to columns doesn't work because it seperates all the text. In this column, I need the text that has more than three spaces between it to be moved. Eg. A1= Flat 1 Sky Way Scotland SW1 A2= 23 Dock Road Docklands DK3 A3= 161 Speed Drive Gasville GV7 Between the first set of text and the second, there is a minimum of 3 spaces. I desperately need the second lot in the second column. The Postcode is two spaces after the town's name which must not be affected, but rather move with the town's nam...

Compare adjacent numbers Within Cells
How do I express the following as a formula: If any number between A1 , B1 and C1 have values that are either one value higher or lower from each other then D1 equals Yes. (ie 213, 049, 281, 809, 345 or 346) but If none of the numbers between A1 , B1 and C1 have values that are either one value higher or lower from each other then D1 equals No. (ie. 246, 513) Another rule: I am using whole number values from 0-9, 0 and 9 are the beginning and ending parameters, therefore, they are one higher or lower to each other ------------------------------------------------ ~~ Message posted from ht...

Email graphics & text problem
I am trying to send an email newsletter that has some text on top of a couloured background. When it is sent the text looks fuzzy and poor, I assume this is becuase the whole thing is converted to jpg or similar. Is there a way to correct this problem. I am using Publisher 2003. Regards Peter Allen Don't waste your time trying, just convert it to PDF and send it that way. People receiving it will be much happier with it in PDF format and you can also be certain it will be readable for everybody. -- But I want it to be automatically displayed in the preview pane etc. so that they...

Excel auto formatting text
Hi, I want to type VTi into a cell, it is changed to Vti, how do I turn off this 'feature'? Thanks in advance tools>auto correct>exceptions -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Optiglaze" <optiglaze@gmail.com> wrote in message news:459250F5h9gqU1@individual.net... > Hi, > > I want to type VTi into a cell, it is changed to Vti, how do I turn off > this 'feature'? > > Thanks in advance > > Thanks Don glad to help -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Optiglaze"...

Text Formatting for URL
In CRM, On the accounts form, we would like to have an field point to a portal site where we would keep more information about a client, I have made the field a URL formatting type however it will not show up as a URL just as text. Any help would be greatly appreciated. Stephen ...

Use a Carriage Return when typing data into a text cell
What is the code for entering a carriage return in a text cell.? (not using a char map lookup) I tried "& char(10) &" and various combinations using alt, with no luck. What I want is to type Far[the_char_return_code]Farley[ENTER] resultng in Far Farley in the same cell. I know it exists, because a long time ago I found a spreadsheet that had it in the cell, and I copied the cell. I have been using that, but going to that worksheet and copying that character is a bit of a pain in the butt. There HAS to be a better way of doing it than that. TIA Far Farley The Profe...

Left Justify CommandButton Text
If I go to the Forms toolbar I can add a button to my form. I can als left justify my button text. I can't seem to do that if add a butto from the Control Toolbox. The text just remains centered on th button. Is there a way to Left Justify the text on a command button? Thanks, Crai -- cparson ----------------------------------------------------------------------- cparsons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=26295 hi, this is done on the commandbuttom's property sh...