Mark duplicate entries in google spreadsheet

Question

I have a table with entries in column F, which can later be duplicated in F. I want to do 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 for doing this work with the built-in script of Google apps?

Disclaimer: I am not familiar with JS, but I try.

Answer 1

You do not need JS for this. You can do this with built-in spreadsheet formulas.

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

Do you want to:

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

A key note is the use of AND forumla.

This will fill up and look like this:

 =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 table data as an example. Does this suggest that the formula was inserted into the Duplicate? (C2) and filled in:

  ABC 1 Contact Name Duplicate? 2 [e-mail] John 3 [e-mail] Repeated Name 4 [e-mail] Jane Yes 5 [e-mail] Repeated Name Yes