We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to define a formula (calc) for: If A1 is exactly found in at least one cell in the range from B1 to B100? [closed]

asked 2017-05-12 10:20:51 +0200

inJesus gravatar image

updated 2020-10-03 22:56:26 +0200

Alex Kemp gravatar image

Here is the cell array C:\fakepath\get2rowswithoutduplicates.png

(edit: activated screenshot)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 22:55:46.456580


Why do you skip first "t" in column A?

JohnSUN gravatar imageJohnSUN ( 2017-05-12 10:46:10 +0200 )edit

Hi JohnSUN, what "Y"?

inJesus gravatar imageinJesus ( 2017-05-12 10:48:47 +0200 )edit

In your screenshot I see "t" in cells A2 and A4. Second value is striked, but first - in cell A2 - not striked

JohnSUN gravatar imageJohnSUN ( 2017-05-12 10:56:36 +0200 )edit

Ah. OK, forget all t in the first column as I erased this type of entries in the meantime. Sorry for my reading mistake (t as Y) ..

inJesus gravatar imageinJesus ( 2017-05-12 12:07:35 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-05-12 10:54:36 +0200

JohnSUN gravatar image

Try formula COUNTIF($B$1:$B$100;A1)>0 Wrong Values With Conditional Formatting

edit flag offensive delete link more


Thank you. I used it. And I could act on it successfully for many doubles.

At the end I realized that some doubles are not recognized by the countif .... e.g. http://www.shortbooks.de/shortbooksde...

inJesus gravatar imageinJesus ( 2017-05-12 13:04:57 +0200 )edit

Yes, it is possible - this line contains several characters (a dot, a question mark) that have a special meaning in regular expressions. In the Help we can read that "The search supports regular expressions...You can switch the automatic evaluation of the regular expression on and off in Tools - Options - LibreOffice Calc - Calculate."

JohnSUN gravatar imageJohnSUN ( 2017-05-12 13:41:35 +0200 )edit

Yes, this finds the rest of them. Thank you. :-)

inJesus gravatar imageinJesus ( 2017-05-12 14:30:01 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-12 10:20:51 +0200

Seen: 102 times

Last updated: Oct 03 '20