How to compare two sheets and get the common rows

[armstrong123]

Hi,

I have a excel file which has two sheets and below is the first sheet and second sheet. I want to compare the two sheets (ex: each row of first sheet with with all the rows of second sheet ) and then get all the common/matching rows.( all the column values should be same in both rows). Please help me in figuring this out

Col-1 Col-2 Col-3
one data11 data12
two data21 date22
three data31 data32
five date45 data69



Col-1 Col-2 Col-3
one data11 data12
two data21 date22
three data31 data36
five date45 data69

So the end result should be matching rows i.e as below

Col-1 Col-2 Col-3
one data11 data12
two data21 date22

[6StringJazzer]

Shouldn't the matches include

five date45 data69

?

It is a simple matter to highlight or otherwise flag the rows that match. But if you need to produce a third sheet that lists the matching rows, I think it's going to require a macro. If you attach a file I can take a further look.

[armstrong123]

Hi,

Thanks for reply. Please see my attachment in excel

[6StringJazzer]

This macro assumes that the first column in both sheets does not have any blanks.

There are no assumptions about how the sheets are sorted. It walks through each row of First looking for a match in Second.

I have added a sheet called Common. Go to that sheet and click the button to run the macro.

[armstrong123]

Thanks a lot. It worked like a charm..