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

[Tom Ogilvy]

=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.

--
Regards,
Tom Ogilvy


"praveen_khm" wrote:

>
> 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
>
>
> --
> praveen_khm
> ------------------------------------------------------------------------
> praveen_khm's Profile: http://www.excelforum.com/member.php...o&userid=30364
> View this thread: http://www.excelforum.com/showthread...hreadid=526091
>
>

[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

[Tom Ogilvy]

Obviously, there are many ways to solve specific problems.

--
Regards,
Tom Ogilvy


"praveen_khm" wrote:

>
> 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
>
>
> --
> praveen_khm
> ------------------------------------------------------------------------
> praveen_khm's Profile: http://www.excelforum.com/member.php...o&userid=30364
> View this thread: http://www.excelforum.com/showthread...hreadid=526091
>
>