Google Sheets - Color duplicates uniquely

Question

I'm using the custom formula:

 =countif(3:3,C3)>1

To text-color duplicate cells, but both a/a and b/b are coloured the same. Is it possible to have each unique set of duplicates be a different color?

Answer 1

No, you can't do this, the only option is to use script. I suggest using Gradient Conditional Formatting in another column.

---

Step1.

Paste column next to your column with data, column B in this sample. Suppose, the data column is A, then paste this formula in cell B2:

=ArrayFormula(IFERROR(--(countif(A:A,A2:A)>1)*MATCH(A2:A,A:A),0))

It will expend automatically.

---

Step 2

Make custom number format in column B to make numbers invisible. Set your number format to:

;;;

---

Step 3

Make Conditional Formatting for column B, select gradient and choose appropriate colours:

The result is neat column with conditionally coloured duplicates, each in it's own colour. You may additionally filter column B to select each group of duplicates (but need to make them visible).