Conditional formatting - Highlight words from a li

2019-08-30 04:07发布

I have a list of values in column A named "Our Keywords" and another list of values from multiple columns from competitors named "Competitor keywords"

Using conditional formatting, how can I highlight the keywords that appear in "Competitor keyword" columns but not in "Our keywords" column. And vice versa keywords that are unique to the "our keywords" column.

e.g. problem:

enter image description here

e.g. desired result:

enter image description here

The words that appear in column 1 ONLY are highlighted green and words that appear in the competitor columns but NOT in column 1 are highlighted red.

1条回答
【Aperson】
2楼-- · 2019-08-30 04:49

green color:

  • apply to range A1:A custom fx: =FILTER(A1, NOT(COUNTIF(B1:E, A1)))

red color:

  • apply to range B1:B custom fx: =FILTER(B1, NOT(COUNTIF(A$1:A, B1)))
  • apply to range C1:C custom fx: =FILTER(C1, NOT(COUNTIF(A$1:A, C1)))
  • apply to range D1:D custom fx: =FILTER(D1, NOT(COUNTIF(A$1:A, D1)))
  • etc.

0

查看更多
登录 后发表回答