CODEWITHSUNDEEP
excel
Oli
Oli

Reputation: 542

Excel, returning what's not equal when comparing columns

I have a few columns for example:

a   b   c
1   1   0
1   1   0
0   1   0

So I can easily find out if they are equal or not (row 1 = row 2):

=and(a2=a3,b2=b3,c2=c3)

When doing this for comparing row 2 to row 3 we get FALSE, however I'd like to know a way to find out which column(s) caused the fail. In this case it would return column a.

EDIT

I guess I could check each column individually and then search for FALSE's on that row of results, but seeking something more elegant.

Upvotes: 1

Views: 148

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

You can also use conditional formatting.

Highlight the range A3:C4, Choose Conditional Formatting | New Rule | Use a formula... and enter

=A2<>A3

then choose a format (e.g. fill colour) to highlight the cells that don't match.

The formula automatically changes to =A3<>A4 for the second and third rows of data, so will highlight cell A4.

Upvotes: 1

R.Katnaan
R.Katnaan

Reputation: 2526

Here, I got one for you. This formula is for matching ROW 1 and ROW 2 of your sample data.

=IF(AND(A1=A2,B1=B2,C1=C2),"Matched","Unmatched Column: " & IF((A1=A2),"","A") & IF((B1=B2),"","B") & IF((C1=C2),"","C"))

If you want to matched more than two row, don't worry, put that formula in first row and drag the cell value to the last row. So, every row will filled with related formulas.

I think that the another way is making with excel-vba and no more way to do. If you found the other way, post it. We will vote.

Good Luck.

Upvotes: 2

Related Questions