match two worksheets

[amorrison2006]

Hi there,

I need to match two worksheets and work out what appears in one and
not the other.

The match criteria should be variable. Like I only it to find a match
if the row cells in columns B, C, D, E and S match,

I want to check that whats in Sheet 2 is in sheet 1. there could be
duplicates which is absolutely fine but the macro would need to
consider this. Also I need to check that whats in sheet 2 is in sheet
1.

This will highlight items I do not have in my sheets.....

I hope someone can help.

Thanks so much

Andrea

 

[Dave Peterson]

You don't need a macro.

You could use a formula in a helper cell:

=isnumber(match(1,(b2=sheet1!$b$1:$b$100)
*(c2=sheet1!$c$1:$c$100)
*(d2=sheet1!$d$1:$d$100)
*(e2=sheet1!$e$1:$e$100)
*(s2=sheet1!$s$1:$s$100),0)))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

====
Just a note. These array formulas do a lot of work and can increase calculation
times. Once you get the results (copy down the column) that you need, you may
want to convert those formulas to values.

Then you can apply data|filter|autofilter to see the true (matches) and false
(mismatches) easier.