Page 1 of 1

Automaticly Highlight duplicate values in a column row

PostPosted: Mon Nov 09, 2015 3:21 pm
by MiRaDoR
Hi, I was wondering...
Does Calc provide the same "Conditional Formatting" possibilities found in Excel? For example, I would like to highlight all cells that have duplicate values in a column row? I tried to google this, and threading this forum didn't do it for me.
Example:
click-highlight-cells-rules-duplicate-values.png

Is there an easy way, like this Excel one in Open Office Calc?

Re: Automaticly Highlight duplicate values in a column row

PostPosted: Mon Nov 09, 2015 3:51 pm
by FJCC
Select all of the cells you want to affect and notice which cell the cursor ends up in. That will probably be the first or last cell in the selection. Let's say you highlighted A1:A100 and the cursor is in A100. In the Format -> Conditional Formatting dialog, set the Formula Is to
Code: Select all   Expand viewCollapse view
COUNTIF($A$1:$A$100; A100) > 1

and set the Style to whatever value you want.

Re: Automaticly Highlight duplicate values in a column row

PostPosted: Mon Nov 09, 2015 4:46 pm
by Villeroy
FJCC wrote:COUNTIF($A$1:$A$100; A100) > 1

With more than one column selected you compare this row's value in column A:
Code: Select all   Expand viewCollapse view
COUNTIF($A$1:$A$100; $A100) > 1



In rare cases:
Code: Select all   Expand viewCollapse view
COUNTIF($A$1:$A$100; A$100) > 1

(this column''s value in row 100)

Re: Automaticly Highlight duplicate values in a column row

PostPosted: Fri Jan 29, 2016 2:17 am
by juntjoo
can someone explain how to select cells in more than one sheet? Thanks.

Re: Automaticly Highlight duplicate values in a column row

PostPosted: Fri Apr 26, 2019 4:54 pm
by atxemtp
I have 5 (E-I) columns and 171 Rows (2-172) that I need to insure that there are no duplicate values. If there are duplicate values I want to highlight the duplicate values. How do I do this? I am new to open office. :?

Re: Automaticly Highlight duplicate values in a column row

PostPosted: Thu Dec 05, 2019 7:04 pm
by mircea273
I have selected my 2 columns that I wanted to compare, then I went to "Format->Conditional Formatting->Condition"
There I have selected cell value is "duplicate" "apply style" "Warning" to make the duplicated values red.