Compare 2 Worksheets & Find Matches

[Mark]

Can anyone tell me how to compare 2 worksheets and where
there is an exact match of text in a cell to return a list
of matches? Or in someway highlight the matches so I'll
know the data exists in both sheets? Thanks. I'm using
Office XP.

 

[Ashish Mathur]

Hi,

Let's say you have the following details in cells A1;A5 of
sheet 1. "In sheet 2" appears in cell B1

Name In sheet 2

Ashish
Mahesh
Rakesh

In sheet 2, you have the following: (in the range A1;A5)

Name

Ashish
Mahesh
Prakash

Array Eneter the following formula in cell B3 of Sheet 1.

OR(EXACT(A3,Sheet2!$A$3:A5)).

Array Enter - Press Ctrl+Shift+Enter after typing the
formula.

Now highlight range A1:B5 and go to the following

1. Data
2. Filter
3. Auto Filter

Click on the down arrow in cell B1 and select TRUE. This
will show all the repeated entries

Regards,

Ashish Mathur
Excel - MVP