[Solved] Highlight duplicate text w/ conditional formatting

Discuss the spreadsheet application

[Solved] Highlight duplicate text w/ conditional formatting

Postby marko2002 » Fri Jan 03, 2020 10:35 am

Hello all, just started using OO again after having difficulties with other sheets, and in calc I'm trying to auto highlight duplicate text and though I can see formulas for numbers, etc, I can't see how to do this for text. I have names in column R that goes from R11:R8000 and would like for those names to be highlighted automatically if they appear more than once in that column, could someone please assist in doing this :)
Most appreciated.
Marko
Last edited by robleyd on Sat Jan 04, 2020 12:01 am, edited 3 times in total.
Reason: Add green tick
Open Office 4.1.7 on Windows 10
marko2002
 
Posts: 2
Joined: Fri Jan 03, 2020 10:30 am

Re: Another conditional formatting question!

Postby Villeroy » Fri Jan 03, 2020 2:05 pm

Select R11:R8000 (plus any other columns to be formatted conditionally and notice the active cell. In the following I assume R11 being the active cell.
Condition #1, change "Value Is" to "Formula Is": COUNTIF($R$11:$R$8000;$R11)>1
Notice the absolute reference $R$11:$R$8000 and the mixed row reference $R11. Relative references are relative to the active cell, applied to the entire selection when you click OK. The formula calculates how often this row's value in R occurs in R11:R800 and if this count is bigger than 1 the formula returns True and applies the specified cell style.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Another conditional formatting question!

Postby marko2002 » Fri Jan 03, 2020 7:58 pm

Thank you, perfect :)
Open Office 4.1.7 on Windows 10
marko2002
 
Posts: 2
Joined: Fri Jan 03, 2020 10:30 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests