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
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)