Comparing rows

[praveen_khm]

Hi all,

I need to compare two worksheets. It should be checked in such a way
that all the data in the first ROWin sheet 1 should be the same when
compared with the data in the range of sheet 2. It should not happen
that one cell in row 1 exists and the other does not. Also, the data is
not sorted and even if sorted, it would lead to no help. Can anyone
please help me on this please. I tried to use hlookup which compares
only the data in one cell.
In brief, the data in a row of sheet 1 should match with any COMPLETE
ROW in a range of sheet 2. Please help. I feel this can be done with
formulas but I just landed up in scratching my head. If done
with VB, then too, am much more than happy.
Your help is appreciated.

Thanks,
Praveen

 

[Guest]

=SUMPRODUCT(COUNTIF(Sheet1!1:1,Sheet2!3:3))

will count the matches from row 1 of sheet1 to row 3 of sheet2.

=SUMPRODUCT(COUNTIF(Sheet1!1:1,Sheet2!3:3))=CountA(Sheet1!1:1)

would indicate if all the cells matched.

=And(SUMPRODUCT(COUNTIF(Sheet1!1:1,Sheet2!3:3))=CountA(Sheet1!1:1),CountA(Sheet1!1:1)=CountA(Sheet2!3:3))

would indicate that all cells matched and the row in sheet2 didn't have
additional cells with values.

 

[praveen_khm]

Thanks Tom,

However, this gives the result only if it exists in the same row and
also I was looking for VB as I see that this is very time consuming and
it has taken almost 10 min and is still calculating. Is there any other
way out......

Regards,
Praveen

 

[Guest]

Obviously, there are many ways to solve specific problems.