compare data

[Sigfrido]

Hi folks,

I've got a problem with Excel; I've got 2 worksheets organised like:

Worksheet 1 (list of all users)
Column A: contains surname of all users
Column B: contains name of all users
Column C: contains no data

Worksheet 2 (tracking presences for events)
Column A: contains surname of users
Column B: contains name of users
Column C: contains presence (value “YES”)


In fact, the worksheet #1 contains like 1000 records (rows) ordered by A/Z criteria; the worksheet #2 contains like 50 record (50 rows) ordered by the same criteria.

I'd like a macro/function that:

1) does a comparison between columns surname/name of both worksheets (so, columns A and B)
2) if an identity is noticed, fills the column C (worksheet #1), with the correct value (“YES”) for the right user at the right row where the identity is noticed.

For example

Worksheet #1, row 900, I've got:
Column A (Surname): Pinco
Column B (Name): Pallino
Column C (Present or not): no data

Worksheet #2, row 5, I've got:
Column A (Surname): Pinco
Column B (Name): Pallino
Column C (Present or not): YES

I'd like the macro fills the cell C900 of worksheet #1 with the value “YES”.

Any help would be appreciated.

Thanks in advance!

Regards

[robert111]

in worksheet2 add an extra column that concatenates surname and name, and do the same in sheet1.

then use offset and match to find a match in sheet2 and return yes or no as appropriate

=offset(reference cell,match(cellwithconcatnameinsheet1,myrange,0),4)

myrange is the column in sheet2 with concatnames
4 is the horizontal offset (+ for right, minus for left)