So I am building a spreadsheet and everything was working fine but then I kept tinkering with it and making it more intuitive (and less work on the user) and ran into a snag. What I am trying to do would really be best for a database but I digress.
First I will set the stage:
Column A | Column B | Column C
------------------------------
01 | D1 | Name
01 | P1 | Name
03 | D1 | Name
03 | D1 | Name
So this is in a nutshell what I have. Name
is included but not relevant really. I know that if I do
=countif(A:A, A2)>1
in the conditional formatting, all 4 rows will be red (duplicate). This is cause there are two 01s and 03s. But in the realm of what I am doing this is not a duplicate. It should only be red if there are more than one duplicate matching in A Column containing the same B Column data. So row 1 and 2 are not duplicates but 3 and 4 are cause they both have D1 in the Column B and match in Column A.
I am not entirely sure what I should even look for here but I have spent the past 3 hours looking. Can someone here help me out on this? Thanks in advance.
Note 1:
I have tried something like this but it works but fails my purpose. It returns true because there is in fact more than 1 D1 in column B as there should be. But it doesn't take into consideration I only care about exactly matching column A and B marking red.
=AND(countif(A:A, A2)>1, countif(B:B, B2)>1)
Best Answer
you need to use
COUNTIFS
:=COUNTIFS($A:$A; $A1; $B:$B; $B1)>1
=COUNTIFS($A:$A, $A1, $B:$B, $B1)>1