Compare two columns in two different spreadsheets and Highlight difference

**shilloh** · 01-05-2012, 03:22 AM

Hi

My request is

1) To compare one column A in sheet1 with column A in sheet 2 , once the value of both are equal, then compare Column B of sheet1 and column B of Sheet2, if they are same , do nothing but if they are different , Highlight it with a colour.

2) If the value in column A of sheet 1 is not present in the column A of sheet 2 , then show "New Entry"

Kindly help me with a macro or some formula which can give these results. Spreadsheet attached.

Thanks a lot for your help

Regards
Shilloh

**Fotis1991** · 01-05-2012, 05:07 AM

Hi shilloh

1) You can do it, using a helper column(that you can hide it, if you like) and using countif & if&and functions.

=IF(AND(Sheet2!$A$2:$A$8=A2;Sheet2!$B$2:$B$8=B2);COUNTIF(Sheet2!$B$2:$B$8;B2);"")

Then in column b, use a simple conditional formatting.

2) In column C(Sheet2) use the formula:

=IF(A2<>Sheet1!$A$2:$A$8;"NEW ENTRY";A2& "EXIST IN Sheet1!")

You can add a conditional formatting if you like to it!

Hope to helps you.

**shilloh** · 01-05-2012, 07:33 AM

Hi Fotis

Thanks for your quick response. It works fine when the order of column A doesnt change in both the sheets. But i have a slightly different requirement, if the order in Column A is different in both the sheets, im unable to get the results.

Ive attached the table. Should we use vlookup?

Thanks in advance

**Fotis1991** · 01-05-2012, 10:24 AM

Hi

..Not sure if i get your new point..

If i understoond well, then in Sheet2!E2, use the formula.

=INDEX(Sheet1!$B$2:$B$8;MATCH(A2;Sheet1!$A$2:$A$8;0))

Is this are you looking for???

**shilloh** · 01-06-2012, 01:45 AM

Hi Fotis

This is exactly what I wanted, Thanks a lot!

Regards
Shilloh

**Fotis1991** · 01-06-2012, 04:15 AM

You are welcome. Glad to helped you.

So if you found your solution, pls mark the Thread, as SOLVED.

Compare two columns in two different spreadsheets and Highlight difference

LinkBack

Thread Tools

Rate This Thread

Display

Compare two columns in two different spreadsheets and Highlight difference

Re: Compare two columns in two different spreadsheets and Highlight difference

Re: Compare two columns in two different spreadsheets and Highlight difference

Re: Compare two columns in two different spreadsheets and Highlight difference

Re: Compare two columns in two different spreadsheets and Highlight difference

Re: Compare two columns in two different spreadsheets and Highlight difference

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions