Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
common numbers in 2 columns | Excel Worksheet Functions | |||
find and highlight | Excel Discussion (Misc queries) | |||
match multiple criteria and highlight corresponding row in Excel | Excel Worksheet Functions | |||
Highlight text | Excel Discussion (Misc queries) | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions |