conditional formatting for multiple words

[xtreme07]

Hi,

i have 2 files: xls, txt

- the xls has 10 columns with 2000 rows each.
each row has a word

- the txt has a list of about 100 words (100 lines). all of these words can be found in the excel list.

i need to use the txt words to highlight the cells that match the excel words.

i would normally introduce each word from the txt, into the conditional formatting by highlighting al the columns and then using the format option from the conditional formatting.

which would take me loads of time to insert 100 rules.

wondering if there's a way to automate this with a formula for conditional formatting.

download files

 

[Andrew Poulsom]

Make a list of the words, say in B1:B100. To format A1 use the conditional formatting formula:

=COUNTIF($B$1:$B$100,A1)

 

[xtreme07]

Make a list of the words, say in B1:B100. To format A1 use the conditional formatting formula:

=COUNTIF($B$1:$B$100,A1)

this work for 1 cell. how do i apply it to columns ?

i've tried
=COUNTIF($B$1:$B$100,$A$1:$A$2000) and it also formated words that it shouldn't with A1 selected

also tried
=COUNTIF($B$1:$B$100,A1) with entire A column selected

both methods formatted words that weren't in the list of 100 words

 

[Andrew Poulsom]

The formula I posted should work for column A provided that A1 is the active cell when you enter it.

 

[xtreme07]

Solved:
i've found this formula
=SUM(COUNTIF(K1,$A$1:$A$100)) which i enetered in the condtional formatting
them i went to manage rules where i entered the cells where the formula should apply to. (something like this K1:S2000 so it can look in multiple columns)


i tried the same thing with your formula Andrew, but for some reason it didn't do the trick. maybe i did / didn't do something .. anyway, ty for your input Andrew, i appreciate it.

 

[Andrew Poulsom]

You don't need an array formula there. The equivalent using my formula would be:

=COUNTIF($A$1:$A$100,K1)

 

[xtreme07]

You don't need an array formula there. The equivalent using my formula would be:

=COUNTIF($A$1:$A$100,K1)

i tried again your formula and it worked. it seemed i forgot to re-edit the formula from the 'manage rules' (under cond. format.), after choosing for which cells to apply the formula.

for some reason the K1 was meesed up into something like 'xey 122234' .. or something like that and i had to re-enter K1 so the formula can work


Edit:

one more question tho ... it seemd that the cell with word ' feeding ' won't be highlighted if i use ' feed ' as a reference
what would be a good formula to highlight the cells that contain the word feed in their cell ?

so the cell would feeding would be selected if it searches for feed or ing, or edin

 

[Andrew Poulsom]

That would be:

=COUNTIF($A$1:$A$100,"*"&K1&"*")

 

[xtreme07]

That would be:

=COUNTIF($A$1:$A$100,"*"&K1&"*")

that would be NOT

for some reason it keeps the highlight as if nothing has changed in the formula; as if it the quotes and the stars wouldn't be there.
so, the word tricky would not be highlighted if the referrence would be the word trick

also tried "*K1*", which failed

 

[xtreme07]

Solved:

=MAX(INDEX(COUNTIF(K1,"*"&$AC$2:$AC$54&"*"),0))

where: $AC$2:$AC$54 are the cell with words from text file

found here: http://www.eggheadcafe.com/conversationwarning.aspx?messageid=31339508&groupid=594