Link to home
Start Free TrialLog in
Avatar of Software Squirrel
Software SquirrelFlag for United States of America

asked on

Compare values in two Excel Sheets

I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
Avatar of Brian B
Brian B
Flag of Canada image

Sounds like you should use a vlookup or xlookup.
You might want to attach an example showing the expected results.
Avatar of Software Squirrel

ASKER

Here is an example.
example.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Saqib Husain, Syed  , That was almost perfect. One strange issue is that out of 450 lines, there are 5 entries that show they match and also say NO they are not in both. I verified they are. All the others that show up as NO are unique.
Make sure they do not have spaces before or after the text in both columns.
Thanks for the points..
Saqib Husain, Syed , That is what I was thinking but didn't seem to help. It's ok. I can just ignore those. It accomplished the goal. Thanks again. 
Would be interesting to know what's wrong. Could you please upload an excel file keeping only the erroneous rows with a few more rows for testing?
Avatar of Yolanda Cuesta Altieri
Yolanda Cuesta Altieri

Hello, I agree with Tom using conditional formatting. Did you try it?
Regards,
Yolanda Cuesta
@Saqib Husain, Syed or anyone who wants to reply. What if I have a scenario where there are two columns The first column has a list of URL's and the second column has a list of URL's.  The first column has about twice as many values as the second column.  There are values in the first column that match the second column.

I would like to know what is missing in the second column, which is in the first column.  Also, if there values in the second column, not in the first column, I would like to know that as well. 
Are you familiar with Power Query (embedded in Excel)?  You could make two tables: one out of the first column and one out of the second column.  Taking the tables into Power Query would enable you to make comparisons both ways.
You can modify the upper formula for:

What is missing in the second column
=IF(ISERROR(MATCH(A2,$B$2:$B$9,0)),A2,"")

What is missing in the first column
=IF(ISERROR(MATCH(B2,$A$2:$A$9,0)),B2,"")