MS Excel: query for rows where cell A & B are identical but C is different?

258
Meltemi

I'm trying to clean up some really bad data that I inherited and I'm looking for a way to 'ask' Excel to somehow bring to my attention all rows where cell A & B match but cell C is different.

Example:

 A B C 1 cat dog cow 2 cat rat cow 3 cat pig ant 4 cat dog pig 5 cat dog cow 6 cat rat cow 7 gnu rat cow

Put another way. I want rows 1, 4 & 5 to be brought to my attention because A & B match but C is different. I'd settle for just one of those (1, 4 or 5) to be brought to my attention as I can dig in and figure out the rest.

Thoughts?

0

2 ответа на вопрос

1
teylyn

So, you want to highlight row 4, since that is the only row where not all three values match. Cat and dog are also in rows 1 and 5, but in these, the value in column C is the same.

You could use this formula in D1 and copy down:

=IF(OR(AND(SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1))=2,SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1),--($C$1:$C$7<>C1))>0),SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1),--($C$1:$C$7<>C1))>1),"here","")

(It's not an array formula).

Or use Conditional Formatting on the cells C1:C7

enter image description here

The formula is 

=OR(AND(SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1))=2,SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1),--($C$1:$C$7<>C1))>0),SUMPRODUCT(--($A$1:$A$7&$B$1:$B$7=A1&B1),--($C$1:$C$7<>C1))>1)

I added a case where A and B are the same in only two rows and C is different for these two rows. These two should be highlighted as far as I understand the task.

Очень красиво сделано datatoo 11 лет назад 0
0
nickjamesuk

There are no matching A and B in your example, but the formula: =If(And(A1==B1,A1!==C1),"A1 and B1 match, but C1 doesn't","Other") Will identify what you want.

Я вижу замешательство. `A` не * равно * (` = `)` B`, но `A + B` равно в этих строках. Хм ... это новое определение может привести меня к решению ... Meltemi 11 лет назад 0
И строки A, и строки B одинаковы для 1, 4 и 5. Я хотел бы знать, когда это так, но только если строка C как-то отличается в любой строке, где A и B совпадают. Надеюсь, что это имеет смысл ... что в этом случае было бы так для 1, 4 и 5, но * не * 2, 6 (и 3 & 7 в этом отношении) Meltemi 11 лет назад 0
Ооо, вам понадобится формула массива для этого ... nickjamesuk 11 лет назад 0

Похожие вопросы