Conditional formatting, search formula

Question

For reference, I have two data sets, one that with scraped names and one with manually input names. The Manual input set has broader coverage but the scraped set is more accurate.

So I have used textjoinif to create a list of the manually input towns for each company, I have the scraped towns each in its own cell in the same row. I want to have conditional formatting where each of the scraped values will be searched in the textjoin string associated with the same ID (same row), and highlighted green if there is a match.

It's entirely possible I'm making this more difficult than it needs to be, but this is being used for several thousand IDs and many thousand contacts

Here is the example I was using

                                                  Count   Towns
A01 Dunkin Donuts   Norwell          Dunkin Donuts  6   
A02 Honey Dew       Hanover          Honey Dew      3   
A01 Dunkin Donuts   Springfield      Beard Papa     2   
A03 Cronut          Walnut Creek     Cronut         2   
A04 Beard Papa      Culver City         
A03 Cronut          Santa Monica            
A01 Dunkin Donuts   Summerville         
A02 Honey Dew       Charlestown         
A04 Beard Papa      Oakland         
A01 Dunkin Donuts   Dorchester          
A01 Dunkin Donuts   Jamaica Plain           
A01 Dunkin Donuts   San Francisco           
A02 Honey Dew       Agoura

In your photo, what is the input (starting data)? What is the output? — urdearboy, Sep 17 '18 at 23:42
My bad, so Column A-C are a dramatically simplified example of the two data sets, F is the textjoin product (string), and G-I are the scraped (and therefore most reliable spellings). — Colter Miller, Sep 17 '18 at 23:45
@urdearboy I will work on clearing things up a bit, it's an example that I'm trying to improvise so definitely needs some work — Colter Miller, Sep 18 '18 at 00:13
highlight the cells manually that you would like to have highlighted automatically so we know what the desired out put is. — Forward Ed, Sep 18 '18 at 00:15
Thanks @Forward Ed, I made some clarifying edits to the question itself and attached a new image showing the highlights, — Colter Miller, Sep 18 '18 at 00:22

Forward Ed · Accepted Answer · 2018-09-18T00:42:25.283

3

highlight the entire range you want to apply the conditional formatting and ensure that the upper left hand corner of the selection set is the active cell.

go to conditional formatting and choose custom formula.

In the custom formula bar enter the following

Case sensitive
=FIND(G2,$f2)

NOT case sensitive
=SEARCH(G2,$f2)

apply your colouring and whatever other formatting you want and click ok/apply until you are back at your spreadsheet.

edited Sep 18 '18 at 00:42

answered Sep 18 '18 at 00:36

Forward Ed

9,484
3
22
52

1

This is better :) – urdearboy Sep 18 '18 at 00:50
1

I was not sure until I tested it what would happen when it was not found and an error was returned. Turns out its treated the same as False. And as @Jeeped once said, 0 = false, and everything is TRUE – Forward Ed Sep 18 '18 at 00:52

score 1 · Answer 2 · answered Sep 18 '18 at 00:49

1

Conditional Formatting > New Rule > Use a formula to determine which cells to format > Use below formula > Apply to desired range

=COUNTIF($A$1,"*"&B1&"*")

answered Sep 18 '18 at 00:49

urdearboy

14,439
5
28
58

1

Can you guess which state i'm from? – urdearboy Sep 18 '18 at 00:53

score 1 · Answer 3 · answered Sep 18 '18 at 05:03

Please select and clear any CF from ColumnsG:I and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(G1<>"",SEARCH(G1,$F1))

Format..., select highlighting of choice, OK, OK.

Conditional formatting, search formula

3 Answers3