compairing 2 lists

[Randy F]

I have 2 lists of names. Sheet1 clmA contains a list of
names of employes that have done FSR's this week. and
sheet2 clmA contains a list of names of all employes and
clmB home office, clmC Job Title.
What i am doing is finding the name on sheet1 then find
the same name on shee2 then copy the home office and job
title to sheet1,
example
sheet1 A40 has John Doe
sheet2 A10 has John Doe and B10 has his home office, and
c10 has his job title
I would like to copy sheet2 B10 & C10 to sheet1 B40 & C40

I havs aprox 200 employes to sort each time

I have a macro that will sort row1 but i can't get it to
step through all 200,

my macro
Sub www()

Dim clmA, clmB
For clmA = [names!a2] To [names!a200]
For clmB = [names!b2] To [names!b200]
For Each c In [Data!a2:a200]
If c = [clmA] Then c.Offset(0, 3) = [clmB]
Next step, clmA, clmB

Next

End Sub

 

[Dave Peterson]

There's a builtin worksheet function that might work even better: =vlookup().

So in sheet1, column B, put this kind of formula:

=vlookup(a1,sheet2!$a$1:$c$9999,2,false)
and drag down
In column C, retrieve the 3rd column:
=vlookup(a1,sheet2!$a$1:$c$9999,3,false)

If there's no match, you'll get #n/a's back. You can hide them by wrapping your
vlookups() with iserror:
=if(iserror(vlookup(....),"Missing",vlookup(....))

And if there's a blank cell in your table, it'll be returned as a 0.

You can hide that with:

=if(iserror(vlookup(...),"Missing",If(vlookup(...)="","",vlookup(...)))

drop the original vlookup formula into each of those vlookup(...) place holders.

And shouldn't there be more people doing FSRs? (what the heck is an FSR?)

I have 2 lists of names. Sheet1 clmA contains a list of
names of employes that have done FSR's this week. and
sheet2 clmA contains a list of names of all employes and
clmB home office, clmC Job Title.
What i am doing is finding the name on sheet1 then find
the same name on shee2 then copy the home office and job
title to sheet1,
example
sheet1 A40 has John Doe
sheet2 A10 has John Doe and B10 has his home office, and
c10 has his job title
I would like to copy sheet2 B10 & C10 to sheet1 B40 & C40

I havs aprox 200 employes to sort each time

I have a macro that will sort row1 but i can't get it to
step through all 200,

my macro
Sub www()

Dim clmA, clmB
For clmA = [names!a2] To [names!a200]
For clmB = [names!b2] To [names!b200]
For Each c In [Data!a2:a200]
If c = [clmA] Then c.Offset(0, 3) = [clmB]
Next step, clmA, clmB

Next

End Sub