1

I have dozens of columns where I want to highlight duplicates. I have an issue with the formula. Here is what I have:

=COUNTIFS($B$3:$B,$C$3:$C,$D$3:$D,$E3:$E,$F3:$F,$G$3:$G,$H$3:$H,$I$3:$I,$J$3:$J,$K$3:$K,$L$3:$L,$M$3:$M,$N$3:$N,$O$3:$O,$P$3:$P,$Q$3:$Q)>1

I have also tried this:

=COUNTIFS($B$3:$B,$B3:$B,$C$3:$C,$C3:$C,$D$3:$D,$D3:$D,$E$3:$E,$E3:$E,$F$3:$F,$F3:$F,$G$3:$G,$G3:$G,$H$3:$H,$H3:$H,$I$3:$I,$I:$I,$J$3:$J,$J3:$J,$K$3:$K,$K3:$K,$L$3:$L,$L3:$L,$M$3:$M,$M3:$M,$N$3:$N,$N3:$N,$O$3:$O,$O3:$O,$P$3:$P,$P3:$P,$Q$3:$Q,$Q3:$Q)>1

Google sheets says: Invalid formula. What is wrong with it? Is there an easy way of doing it instead of typing all the columns one by one?

player0
  • 124,011
  • 12
  • 67
  • 124
Piotr Ciszewski
  • 1,691
  • 4
  • 30
  • 53
  • Hello, does [this](https://stackoverflow.com/questions/21899516/how-to-highlight-cell-if-value-duplicate-in-same-column-for-google-spreadsheet?rq=1) answer your question? – Jescanellas Jun 17 '20 at 11:12
  • Hi @Jescanellas. No it does not. I want to go through all selected columns, not only the one. Also every cell has no relation with any other. – Piotr Ciszewski Jun 17 '20 at 11:14
  • @PiotrCiszewski It does answer your question, or please provide a test sheet so the difference could be seen. – kishkin Jun 17 '20 at 11:33
  • 1
    You need to set the custom formula for conditional formatting for the range `B3:Q` to `=COUNTIF($B$3:$Q, B3) > 1`. Looks like [this](https://i.imgur.com/j59RSPA.png). – kishkin Jun 17 '20 at 11:36
  • Thanks @kishkin ! This sorts out the problem. As I have a lot of data and want to make sure it works ok - my data starts from B3 and ends on S22 - is this the right formula(?): =COUNTIF($B3:$S,B3)>1 (applied to B3:S22) ? – Piotr Ciszewski Jun 17 '20 at 11:43
  • Link to sheet: https://docs.google.com/spreadsheets/d/1STyN8JiWXR5HBvCtmeUeesKJWWWxNuuy4VkS__dDq5k/edit?usp=sharing – Piotr Ciszewski Jun 17 '20 at 11:47
  • @PiotrCiszewski If you want to check everything from row 3 to the very bottom then use `=COUNTIF($B$3:$S, B3) > 1` applied to `B3:S`. If you want to handle only the range `B3:S22` then apply to it `=COUNTIF($B$3:$S$22, B3) > 1`. Do not forget those `$`s. – kishkin Jun 17 '20 at 12:00
  • 1
    Thank you. This is great @kishkin. All works perfectly. I don't know if I can accept the answer in comments, but I would most certainly do if you post this as an answer. Cheers – Piotr Ciszewski Jun 17 '20 at 12:04
  • Why someone downvoted my question? I can't see the reason – Piotr Ciszewski Jun 17 '20 at 12:06

2 Answers2

3

Apply a custom formula for conditional formatting.

If you want to check everything from row 3 to the very bottom then use

=COUNTIF($B$3:$S, B3) > 1

applied to B3:S.

If you want to handle only the range B3:S22 then apply to it:

=COUNTIF($B$3:$S$22, B3) > 1

Do not forget those $s.

kishkin
  • 5,152
  • 1
  • 26
  • 40
0

try:

=REGEXMATCH(""&B3, TEXTJOIN("|", 1, $B$25:$B$28))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I think OP just meant to manually show those "data1-4" as the duplicates that are there, not the list of strings to search for. – kishkin Jun 17 '20 at 12:16