Import data from MAS 90

[John]

I have two worksheets 1) Data Summary 2) EC_GrossHours

Data Summary sheet contains SS#, Name, etc...
EC_GrossHours that contais SSN and other empolyee payroll data
imported from MAS 90.

Is there a way to format the record that gets imported into excel from
Microsoft Query. All my social security numbers get imported as text
(I think) and I have to copy an empty cell and paste special|add to
get the my VLOOKUP function (See below on Data Summary Worksheet)to
work correctly.

=IF(ISNA(VLOOKUP(A2,EC_GrossHoursLookup,8,0)),0,VLOOKUP(A2,EC_GrossHoursLookup,8,0))

A2 = SSN formatted as text
EC_GrossHoursLookup = my defined range that is imported data

A2 is on Data Summary sheet
EC_GrossHoursLookup refers to worksheet EC_GrossHours.

I tried coping A2 and paste special | formats on SSN on EC_GrossHours.

The function only recognizes the SSN when I copy an empty cell and
paste special|add to SSN column in EC_GrossHours.

 

[Debra Dalgleish]

You can convert the SSN on the Data Summary sheet to text in the VLookup:


=IF(ISNA(VLOOKUP(TEXT(A2,"0"),EC_GrossHoursLookup,8,0)),0,VLOOKUP(TEXT(A2,"0"),EC_GrossHoursLookup,8,0))

 

[John]

Thanks Debra
Your adjustment to my formula worked unless the SSN starts with a
zero. If you have any other ideas I would appreciate it.

Thanks again
John

 

[Dave Peterson]

What do the SSN's look like?

Maybe either:

=if(isna(vlookup(text(a2,"0000000000"),......
or
=if(isna(vlookup(text(a2,"000-000-0000"),.....

 

[John]

Thanks Dave that works

Thanks for the help

John