VLOOKUP/Index&Match data format issue

[oscar.olipane]

I created a CSV file from a medical database program at work. I
converted the csv file into an excel spreadsheet (A.xls). The social
security number format in this file defaulted to ###-##-####. The
number of records totalled ~2100.

I received another excel file (B.xls) where the social security number
format is ####### (no dashes). The number of records totalled ~1400.

That means ~700 records are not showing up on B.xls.

I want to identify which 700 from A.xls is not on B.xls. I created
formulas using VLOOKUP and INDEX & Match and neither worked.

What I found is if I manually added the dashes to a record in B.xls
(as well as the extra zeroes at the beginning of the social) then the
formula worked. What this is telling me is I have a format issue
between the two files. It tells me the only solution I am aware of is
to manually add the dashes to the cell to make the formulas work.

(I've tried the format cell --> SSN ###-##-#### function already. The
data input doesn't change, only the "look" on the spreadsheet changes;
so the formula doesn't match the two types.)

Do I need to manually add the dashes to every SSN or is there an
easier way? Thanks

 

[Peo Sjoblom]

First check if one is a number and the other is text

=ISNUMBER(A2)

will return TRUE or FALSE, if the values are TRUE for one file and FALSE in
the other you could depending on which it is fix that. If the values you are
using to lookup are numbers and the others are text you can make them text

=VLOOKUP(TEXT(A2,"000-00-000"),Lookup_Range,2,0)

or something or select all number on the text version and do an edit>replace
and replace - with nothing then use the SSN formatting

If both are text then you probably have hidden characters like trailing
spaces or trailing line feeds (the latter if you downloaded from the web),
Then you could install D McRitchie's TRIMALL macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

same site has info on how to install macros (there is a search section and
if you search for install macros you should find it)

 

[Dave F]

Assume social security number is in A1:
=concatenate(left(a1,3),"-",mid(a1,5,2),"-",right(a1,4))

And be aware that XL is not a secure program and social security numbers
should not be stored in it.

Dave

 

[Allan]

First check if one is a number and the other is text

=ISNUMBER(A2)

will return TRUE or FALSE, if the values are TRUE for one file and FALSE in
the other you could depending on which it is fix that. If the values you are
using to lookup are numbers and the others are text you can make them text

=VLOOKUP(TEXT(A2,"000-00-000"),Lookup_Range,2,0)

or something or select all number on the text version and do an edit>replace
and replace - with nothing then use the SSN formatting

If both are text then you probably have hidden characters like trailing
spaces or trailing line feeds (the latter if you downloaded from the web),
Then you could install D McRitchie's TRIMALL macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

same site has info on how to install macros (there is a search section and
if you search for install macros you should find it)

--
Regards,

Peo Sjoblom












- Show quoted text -

You are both wonderful! Thanks - If I could, I would buy you two
lunch =). Have a wonderful day!

Allan