Filter

[Himu]

I have an excel sheet with 2 tabs. Both the tabs contains a list of names. I
want to compare the names column on both the tabs and make a list of all
names that matches and doesn't match.

Please help.

Himu

 

[Pete_UK]

Assuming your names are in column A on both sheets, you can use a
formula like this in Sheet1:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"No","Yes")

and like this in Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"No","Yes")

In both cases you would put the formula in a helper column (eg in F1)
and copy it down to cover the names in that sheet. It will give a Yes
where the name matches and No for no match.

You could then apply Autofilter to column F, and select Yes from the
drop-down to give you a list of names which have a match in the other
sheet. You could copy/paste the visible names to a new sheet if you
want a separate list.

If you select No from the filter drop-downs you will get a list of
those names that do not match.

Hope this helps.

Pete

 

[Himu]

Thanks Pete!

Assuming your names are in column A on both sheets, you can use a
formula like this in Sheet1:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"No","Yes")

and like this in Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"No","Yes")

In both cases you would put the formula in a helper column (eg in F1)
and copy it down to cover the names in that sheet. It will give a Yes
where the name matches and No for no match.

You could then apply Autofilter to column F, and select Yes from the
drop-down to give you a list of names which have a match in the other
sheet. You could copy/paste the visible names to a new sheet if you
want a separate list.

If you select No from the filter drop-downs you will get a list of
those names that do not match.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome.

Pete

Thanks Pete!








- Show quoted text -