CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jake Randall
Jake Randall

Reputation: 1

Google Sheets Match Any Text

In Google Sheets, I have 2 columns (A and B) of text and I'm trying to set up conditional formatting to identify partial duplicates for when these 2 criteria are both met:

and

So, if A2 = "target.com" and B2 = "Big Bonus"

I want it to flag any other cells where A = "target.com" and B = "Bonus Donuts" or "Biggest Exciting Bonus Ever" (because "Bonus" is identified as the duplicate) or "Exciting Big Day" (because "Big" is identified as the duplicate). I need it to be case-agnostic.

Nothing I have tried has even come close to working, so I won't include any of it here.

Sample Data: https://docs.google.com/spreadsheets/d/1DO-0uJRf6MOJ7fJiza5MAmFNIqpCwJ4WMH28j6wp22w/edit#gid=0

Upvotes: 0

Views: 1061

Answers (2)

Aresvik
Aresvik

Reputation: 4620

If you don't want partial word matching (Big in Biggest), try this in the conditional custom formula:

=and($A3=$A$1, regexextract(" "&lower($B3)&" "," "&substitute(lower($B$1)," "," | ")&" "))

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9345

I've added a new sheet ("Erik Help") to your sample spreadsheet, with the following custom CF rule applied to the range A3:B ...

=AND($A3=$A$1, REGEXEXTRACT(LOWER($B3),SUBSTITUTE(TRIM(LOWER($B$1))," ","|")))

$A3=$A$1 should be self-explanatory.

For the rest, you see I used LOWER to make the comparisons caps-agnostic. I applied TRIM, just in case you accidentally added any spaces into the B1 string and then just replaced remaining spaces with the pipe symbol, which is interpreted by REGEXEXTRACT as OR.

Upvotes: 3

Related Questions