dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
462
share rss forum feed


bmantz65

join:2001-07-23
united state

[Excel] Help comparing data in two columns

I am using Excel 2007. Column A has 1,700 names in this format: 'John Smith' and 'Jane A Doe' Most of the names are in the format of 'Jane A Doe'.

Column B has 3,600 names in the format of 'Jane Doe'. All the names are formatted like this so the 'Jane A Doe' in Column A would be the same as 'Jane Doe' in Column B.

I am looking for a way to strip out the middle initial in Column A, so that I can make a better comparison between the two columns to see if the name in Column B appears in Column A.

Thank you for any assistance.



jaa
Premium
join:2000-06-13
kudos:2
Reviews:
·Optimum Online
·Vonage

c1=find(" ",a1) will find the first space.
d1=left(a1,c1) will give you the first name with the trailing space.
e1=find(" ",a1,d1+1) will give your the second space.
f1=mid(a1,e1+1,len(a1)-e1) will give you the last name.
g1=d1&f1 will give you first and last name, no initial.

For Jane A Doe:
c1=5
d1="Jane "
e1=8
f1="Doe"
g1="Jane Doe"

Column G and now be compared to column B.

You will have to work with it a little to handle no middle initial and other things in the data, but you get the idea.
--
NOTHING justifies terrorism. We don't negotiate with terrorists. Those that support terrorists are terrorists.



cmcasey79

join:2000-12-10
Allen Park, MI
kudos:1

2 recommendations

reply to bmantz65

In C1 (or whatever column you want to use to compare to column b), try: =CONCATENATE(IF(ISERR(FIND(" ",A1)),"",LEFT(A1,FIND(" ",A1)-1))," ",IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))
--
- cmcasey79, BrightHouse RoadRunner Turbo, Farmington Hills, MI



bmantz65

join:2001-07-23
united state

said by cmcasey79:

In C1 (or whatever column you want to use to compare to column b), try: =CONCATENATE(IF(ISERR(FIND(" ",A1)),"",LEFT(A1,FIND(" ",A1)-1))," ",IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

This did the trick. Thank you cmcasey79!