Re: VLOOKUP/Index&Match data format issue

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



<oscar.olipane@xxxxxxxxxxxxx> wrote in message
news:1175705892.996041.82720@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: VLOOKUP/Index&Match data format issue
    ... if you search for install macros you should find it) ... to manually add the dashes to the cell to make the formulas work. ... only the "look" on the spreadsheet changes; ...
    (microsoft.public.excel.worksheet.functions)
  • Re: getting rif of apostrophe in front of ss#
    ... I guess the format also includes dashes in between ... >If Not TypeOf Selection Is Range Then Exit Sub ... >> I am trying to compare two lists by social security ...
    (microsoft.public.excel.programming)
  • Re: Custom Cell Formatting
    ... >I don't know of a format that will do this. ... >OR use a macro to add dashes to the left side of your pre- ... >Dim Cell As Range ... >Dim moretext As String ...
    (microsoft.public.excel.misc)
  • Re: Complex regular expression
    ... Pattern: 999-99-999 ... However the user need not enter ALL the digits and dashes as given in ... Whatever numbers and dashes he had entered must match the above format, ...
    (comp.lang.perl.misc)
  • VLOOKUP/Index&Match data format issue
    ... converted the csv file into an excel spreadsheet. ... I received another excel file where the social security number ... What I found is if I manually added the dashes to a record in B.xls ...
    (microsoft.public.excel.worksheet.functions)