Update one excel sheet from another workbook 1

[johnv20]

I have no experience in VBA so please excuse me if this is
a very inane question.

I have 2 workbooks, workbook a contains a list of all employees, in the form FirstName (col a), LastName (col b). Workbook B is a dynamically generated report in the form "LastName, FirstName" (col a). How do I write a macro that will add an X to column c of workbook a if the employee exists in workbook b ? Any help at all would be greatly appreciated.

Thanks

 

[Aeneas]

You don't need macro code at all:

Put this in column "C" and copy it down for all occupied rows:

=IF(ISERROR(VLOOKUP(B1&", "&A1,[Book2]Sheet1!$A:$A,1,FALSE)),"","x&quot



This part: B1&", "&A1

means take content of B1, merge the string ", " and then contents of A1, which will inherently give you last name, first name format.

Be careful with case et. al. - this has to match exactly to get the x.

Cheers,

Aeneas

 

[johnv20]

Perfect thanks

 

[johnv20]

Sorry - just one last question, is it possible to set a counter up on a cell to count the number of hits ??

 

[VBAjedi]

Have a look in the Excel help file at COUNTIF. . . that should do what you need. You can probably just point to Aeneas' formula in column C for the search value.



VBAjedi