VLOOKUP/Index&Match data format issue
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
|