Automaticly Highlight duplicate values in a column row

Discuss the spreadsheet application

Automaticly Highlight duplicate values in a column row

Postby MiRaDoR » Mon Nov 09, 2015 3:21 pm

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?
OpenOffice 4.1.1 on Windows 8.1 Pro
MiRaDoR
 
Posts: 1
Joined: Mon Nov 09, 2015 2:37 pm

Re: Automaticly Highlight duplicate values in a column row

Postby FJCC » Mon Nov 09, 2015 3:51 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7225
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Automaticly Highlight duplicate values in a column row

Postby Villeroy » Mon Nov 09, 2015 4:46 pm

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26975
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automaticly Highlight duplicate values in a column row

Postby juntjoo » Fri Jan 29, 2016 2:17 am

can someone explain how to select cells in more than one sheet? Thanks.
Android OpenOffice 2.5(Apache OpenOffice 4.1.1/X window system 11 release 7.7) & Apache OpenOffice for Windows v.4.1.2
juntjoo
 
Posts: 5
Joined: Thu Jan 28, 2016 8:55 pm

Re: Automaticly Highlight duplicate values in a column row

Postby atxemtp » Fri Apr 26, 2019 4:54 pm

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. :?
Open Office 4 Windows 10
atxemtp
 
Posts: 1
Joined: Fri Apr 26, 2019 4:37 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests