[Solved] Highlight duplicates

Discuss the spreadsheet application
Post Reply
KayDen
Posts: 1
Joined: Mon Jun 28, 2021 7:36 pm

[Solved] Highlight duplicates

Post by KayDen »

 Edit: Split from [Solved] Highlight duplicates. That topic is marked solved. I created your own topic for you.
-- MrProgrammer, forum moderator 
This has been great!

I am trying to do something similar but use a single "search" cell. Using P5 to search for duplicate numbers in column D.

Highlighting the cells D3:D203. Also I have tried simply highlighting P5 and typing in the formula.

So clicking "Format"> "Conditional Formatting" Selecting "Formula is" and typing AND($P5<>"";COUNTIF($D$3:$D$202)) (setting the style to highlight red. This doesn't seem to work. Am I doing something wrong?
Last edited by MrProgrammer on Fri Jul 30, 2021 1:51 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice4.1.9
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Highlight duplicates

Post by Villeroy »

COUNTIF requires 2 arguments.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Highlight duplicates

Post by eeigor »

Maybe it will help
Type of condition: “Duplicate”
The style is applied to the cell if value repeats in defined cell range two or more time.
It works as for number values as for text in cells.
link
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Highlight duplicates

Post by Villeroy »

eeigor wrote:Type of condition: “Duplicate”
LibreOffice only. Not in OpenOffice.

COUNTIF(range;value) counts the occurances of value in range. COUNTIF(range) as in COUNTIF($D$3:$D$202) makes no sense.
btw: COUNTIF ignores empty cells.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Highlight duplicates

Post by eremmel »

I think that you want to list the number that you want to check in P5, and do not want to show all the duplicate numbers. You can accomplish that with the conditional formula:

Code: Select all

=AND($P$5<>"",D3=$P$5,COUNTIF($D$3:$D$202,$P$5)>1)
You have to select the range and make sure that the cell D3 has the dark border.
This formula checks the contents of P5, then checks if P5 matches the referenced cell in D, then checks to see if there are more than 1 cell in row D that has the value of P5.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Highlight duplicates

Post by Villeroy »

Now I understand what P5 is supposed to do! I think, the condition $P$5<>"" is superfluous since COUNTIF ignores blanks and empty strings anyway.
See column E where I test the shortened variant.
Attachments
t105574.ods
(16.35 KiB) Downloaded 164 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply