compare two columns and highlight if they are same

Hi,

Assuming that both columns to be compared are on the same sheet, try this

1. Select the first column of data
2. Press the Ctrl key and select the second column of data
3. Go to Home > Conditional formatting > New Rule > Highlight only Unique or Duplicate Values
4. Select Duplicate and under Format, select any colour under the Fill tab
5. Click on OK/Apply

This will highlight all entries in the two columns which are duplicates.

Hope this helps.

Hi, I am seeking help using Excel 2010, I may be able to use conditional formula but just want to know how or may be is there any formula that can match both columns and if does match than  it should show "match" otherwise it show show the amont that does not match

i just tried this but it does not work.

i'm comparing 2 columns of names (10,000+ of them each column) to see which side-by-side names are different. Most of them are the same while some are different. But after doing as you said, all names got highlighted, both same or different names.

Thank you Ashish Mathur

Good Tip

helped me  great

If you want a row by row comparison, where each element in column A is tested against its corresponding value in column B, use the array formula

=IF(SUM(--(A1:A5=B1:B5))=ROWS(A1:A5),"same","different")

In this case, the order of the elements is important:  {a,b,c,d} is different than {a,c,d,b}.  This formula returns "same" if each element in A is equal to its corresponding element in column B. It will return "different" if columns A and B have different values or the same values in a different order.

If you want to ensure that all of column A exists in column B, but not necessarily in the same row, use

=SUM(--COUNTIF($A$1:$A$5,B1:B5))=ROWS($A$1:$A$5)

This will return TRUE if every element in column A exists in column B, but not necessarily in the same row. If a value exists in B that does not exist in A (or vice versa), the result if FALSE.  The order doesn't matter: {a,b,c,d} is considered the same as {a,d,b,c}.

In both formulas, change the cell references to your ranges.

Both of these formulas are array formulas, so you must press CTRL SHIFT ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces -- Excel will insert them automatically when you press CTRL SHIFT ENTER.  They will not work properly if you do not use CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas.