Search code examples
excelexcel-formulaexcel-2010

Compare cell by cell in one column based on unique value in other column in Excel

Please help me to compare value cell by cell in one column based on the other column. Example:

Column A         Column B

January            No

January            Yes

February           Yes 

February           Yes

March              No

March              Yes

Based on this, I would like to highlight January and March due to its values in column B are not the same.

Thank you.

Solution

  • Probably shorter way to accomplish this, but here's one method:

    Create a column (Column C) an input this formula into cell C2 and drag it down.

        =IF(AND(A2=A1,B2=B1),0,1)

    Then highlight your data in column A, go to conditional formatting and use a formula such as this:

        =SUMIF(A:A,A2,C:C)>1

    Results:

    enter image description here