Ask Your Question
0

LibreOffice Calc - Partial Match Column Compare Question

asked 2018-12-31 22:49:54 +0200

IvanWatkins gravatar image

How can I perform a case insensitive partial match from one column to the next? Example.

Column A

green

red

brown

Column B

The green tree

Orange Leaves

Brown Dirt

I would like to search all the phrases in Column B with the words in Column A.

Desired Outcome:

The Phrase "The green tree" in column B should be identified because the word "green" from column A was found.

The Phrase "Brown Dirt" in column B should be identified because the word "brown" from column A was found.

I've scoured the web looking for a solution and I haven't found one that can be easily used without the knowledge of writing code.

edit retag flag offensive close merge delete

Comments

What is "identified"? shown in a dialog box when you click a button? appear in a dedicated cell? its cell highlighted on entry?

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-01 12:01:41 +0200 )edit

Identified can mean anything that lets me know there was a match. I.e. Highlighted or a word displayed in an adjacent cell to indicate that there is a match so that I can then work with those entries.

IvanWatkins gravatar imageIvanWatkins ( 2019-01-01 12:55:29 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-01-03 12:17:54 +0200

You may use this formula (for B1):

=SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$3;B1)))

It may be used in an adjacent column (and then the number it shows would indicate how many matches there were), or as a condition in conditional formatting for B column (and then you may highlight the cells with matches).

But keep in mind that, naturally, the formula involves searching for every A-cell text in the B-cell, so it has high computational complexity. Increasing A-range in the formula will quadratically increase computation time.

edit flag offensive delete link more
0

answered 2019-01-05 14:16:33 +0200

IvanWatkins gravatar image

Thanks so much. This works well. Solved.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-31 22:49:54 +0200

Seen: 71 times

Last updated: Jan 05