Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing text strings
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 wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing text strings
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" wrote in message ... On Fri, 27 Nov 2009 11:12:01 -0800, Comparing columns with text <Comparing columns with 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_Yor...gence_Sys tem --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing text strings
On Sat, 28 Nov 2009 13:25:39 -0500, "Rick Rothstein"
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing text strings
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas are becoming text strings | Excel Discussion (Misc queries) | |||
problem with comparing strings in VBA | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
Combining text strings | Excel Worksheet Functions |