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?

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

inJesus gravatar image
edit retag flag offensive close merge delete


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.

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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 47 times

Last updated: May 12 '17