Mark Duplicate Entries in Google Spreadsheet

Question

I have a spreadsheet with entries in column F that could be duplicates later on in F. I'm looking to make something that does something like this pseudocode:

While Ax is not empty

If value in Gx is empty   
  If cell Ex is identical to other cell Ey
  OR 
  If cell Fx is identical to other cell Fy 
     THEN
       Mark Gy as duplicate
       italics row y

Any recommendations on making this work using Google's built in app scripting?

Disclaimer: I'm not familiar with JS but I'm trying.

Answer 1

You don't need JS for this. You can do it with the built-in spreadsheet formulas.

It sounds like you want a similar answer that I gave to this question, with the difference being that you are checking two columns instead of just one.

You want this:

=if(AND(COUNTIF($A$1:$A2,A2)=1, COUNTIF($B$1:$B2,B2)=1), "", "Yes")

The key thing to notice is the use of the AND forumla.

This will fill down and look like this in subsequent rows:

=if(AND(COUNTIF($A$1:$A3,A3)=1, COUNTIF($B$1:$B3,B3)=1), "", "Yes")
=if(AND(COUNTIF($A$1:$A4,A4)=1, COUNTIF($B$1:$B4,B4)=1), "", "Yes")
...

And these are the results using your spreadsheet data as an example. This is assuming the formula was inserted into the Duplicate? column (C2) and filled down:

   A                          B               C
1  Contact                    Name            Duplicate?
2  [email protected]          John  
3  [email protected]   Repeated Name 
4  [email protected]   Jane            Yes
5  [email protected]         Repeated Name   Yes