Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows
=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))=Co untA(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing data in different rows | Excel Worksheet Functions | |||
comparing two rows, then highting? | Links and Linking in Excel | |||
comparing rows and deletion | Excel Programming | |||
Comparing rows in three sheets | Excel Programming | |||
Comparing two rows | Excel Programming |