Software Squirrel

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.

ASKER

Here is an example.

example.xlsx

example.xlsx

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

ASKER

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?

Hello, I agree with Tom using conditional formatting. Did you try it?

Regards,

Yolanda Cuesta

Regards,

Yolanda Cuesta

ASKER

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

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,"")

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,"")

You might want to attach an example showing the expected results.