How to find and highlight common text in multiple columns
 

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!

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!

How to find and highlight common text in multiple columns
 

I tried what you suggested but didn't get the expected results.
Here is a snippet of the actual data I am working with:
Boats Fishing Boats Bass Fishing Boats
Boats Fishing Boats Inshore Sal****er Fishing
Boats Fishing Boats Offshore Sal****er Fishing
Boats Fishing Boats Other Freshwater Fishing

When I used your suggestion, I end up with
Boats Fishing Fishing
Boats Fishing Fishing
Boats Fishing Fishing
Boats Fishing Fishing

The result I want--I want to HIGHTLIGHT say in RED to review the words
"Boats" and "Fishing" so I can make a decision whether to go ahead and make
the change to:
Boats Fishing Bass
Boats Fishing Inshore Sal****er
Boats Fishing Offshore Sal****er
Boats Fishing Other Freshwater

Please advice.
Thanks!


"Teethless mama" wrote:

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!