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

Comments

Hi, The same formula if we need to use for multiple data search in B1 to B200 for data in A1 to A100 how do we code

Macho gravatar imageMacho ( 2021-02-25 04:00:36 +0200 )edit
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: 330 times

Last updated: Jan 05 '19