How to find and highlight common text in multiple columns
Based on your example
A1=Boats, B1=Fishing Boats, C1=Bass Fishing Boats
In A2 =A1
In B2 =TRIM(SUBSTITUTE(B1,A2,""))
In C2 =SUBSTITUTE(SUBSTITUTE(C1,A2,""),B2,"")
"ToExcelAtExcel" wrote:
I have three columns as follows:
A B
C
1 Boats Fishing Boats Bass Fishing Boats
2 Boats Fishing Boats Inshore Sal****er
Fishing
3 Boats Fishing Boats Offshore
Sal****er Fishing
4 Boats Fishing Boats Other Freshwater
Fishing
I want to find and highlight if the text value in cell A2 is repeated in
cell B2 and C2 and if text value in cell B2 is repeated in the cell C2. So in
the above case, I want the word "Boats" in B2 and C2 highlighted and I want
the word "Fishing" in C2 highlighted. The goal is identify redundancy:
Old
----
A B
C
1 Boats Fishing Boats Bass Fishing Boats
-------------------------------------------------------------------------
New
----
A B
C
1 Boats Fishing Bass
I have hundreds of records and the values in cell A1:A900 changes so I can't
hardcode the text in the conditioning formatting statement. Any advice is
welcome! Thanks!
|