Conditional formatting: highlighting duplicates across an array

[jeshkhol]

LO 24.8.3.2
macOS 12.7.6

In a sheet with 50 rows and columns A to F, I need Calc to highlight (= apply a cell style to) any four horizontally adjacent cells in columns A to D that are duplicates. So in test-duplicates.ods (20.1 KB), cells A2:D2 and A29:D29 should be highlighted.

Also, if in row 51 I continue adding data, Calc should highlight such cells whenever the data just entered have already been entered in any four horizontally adjacent cells in columns A to D above.

I’m trying to achieve this using Conditional Formatting (Format > Conditional > Condition, is duplicate), but keep failing: all I can do is to have Calc highlight duplicates that are found within a column, but not those that are found across several columns. AutoCalculate is enabled.

How can I achieve what I need with conditional formatting?

(I have already found a way to find such duplicates with a formula, but using conditional formatting would probably be easier – if it works at all, that is.)

[Wanderer]

@jeshkhol

I have already found a way to find such duplicates with a formula, but using conditional formatting would probably be easier

?? Set conditional formatting to “formula is” and use your formula then. Not easier, but should work…

[mikekaganski]

One often-unobvious thing is, that when you create a formula for conditional formatting, you need to do it as if you are entering it for the top left cell of the formatted range, and then expanding it to the whole range (so use the references as for the top left cell, and pay attention to the relative/absolute addressing).

[jeshkhol]

@Wanderer, I can use the formula only when the table is sorted. I wish to find a way finding duplicates the moment I enter them, without having to sort the table first.

[mikekaganski]

… and how does it prevent the suggestion by @Wanderer, to use the formula in conditional formatting, from working? Or is your formula created in such a way that it only considers adjacent rows? You never shown it. And it’s not in the sample that you provided, it seems?

[jeshkhol]

@mikekaganski, I can use the formula only when the table is sorted. I wish to find a way finding duplicates the moment I enter them, without having to sort the table first.

[mikekaganski]

@jeshkhol does literal repeating of your previous comment make it more clear somehow, answering my specific questions above?

[jeshkhol]

@mikekaganski Sorry, I mistakenly assumed that you did not see it.
Correct, the formula is not in the sample I provided. The reason for this is that it would not work in a non-sorted table.
With a sorted table, I use a separate column with the formula
=IF(OR(SUMPRODUCT(A2:D2=A1:D1)=5,SUMPRODUCT(A2:D2=A3:D3)=5),"duplicate!",""). (It probably could be simplified but I’m still a beginner.)

[mikekaganski]

Try this formula:

COUNTIFS($A:$A;$A2;$B:$B;$B2;$C:$C;$C2;$D:$D;$D2)>1

[Villeroy]

ask114136.odb (13.7 KB)
Open the attachment and the form therein.

[jeshkhol]

@mikekaganski This works exactly as expected. Thank you.

[jeshkhol]

Thank you, @Villeroy. I’m sure this would work as well, however for the moment I need a solution to work within LO Calc, which has just been provided.