check column and alert if cell does not contain words in a list

[gibsongk55]

Hi,

Is there a way I can check a column in excel and alert me of a cell that does not equal any words in a list.

Example:

Word List: brown, blue, green, red, black

If a cell in column F does not contain any of these words, alert me.

Thank you for help,

Gibs

 

[T. Valko]

Hi,

Is there a way I can check a column in excel and alert me of a cell that does not equal any words in a list.

Example:

Word List: brown, blue, green, red, black

If a cell in column F does not contain any of these words, alert me.

Thank you for help,

Gibs

Maybe something like this.

List the keywords in a range of cells:

• D2 = brown
• D3 = blue
• D4 = green
• D5 = red
• D6 = black

Then, assume the range of cells to check is A2:A10.

=IF(SUMPRODUCT(--(ISNA(MATCH(A2:A10,D2:D6,0)))),"Alert","")

 

[gibsongk55]

Thanks,

how could i put that into a script to run as a macro?

I put the colors into sheet: color in cells A1:A8


Gibs

 

[Nalani]

deleted

 

[gibsongk55]

HI Nalani,

Thanks but that script checks for duplicates. I want to check that the data entered in each cell in a column equals one of the words i placed here: color!A1:8

If a cell does not equal one of the colors to alert me.

Example of colors:

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">Brown</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Black</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Red</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Gray</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Green</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Blue</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Hazel</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Blonde</td> </tr> </tbody></table>

When i run a macro it should check all cells in column J, if it does not equal one of the above colors, alert me.

Thanks,

Gibs

 

[Nalani]

Thanks but that script checks for duplicates.

Yea, had a brain malfuction. I deleted my first post.

 

[gibsongk55]

By the way I did try the code you first gave me in validation

=IF(SUMPRODUCT(--(ISNA(MATCH(J3:J10000,Z1:Z10,0)))),"Alert","")

i placed colors in cells Z1 to Z10 and when i entered a correct color it said it is not valid.


Gibs

 

[snoopyhr]

Not a clean one but I tink it works...

Excel Workbook
	S	T	U	V
1	List		Input
2	Brown		Purple	Not on the List
3	Blue		Blue
4	Green		Lavnder	Not on the List
5	Red
6	Black
Sheet2

 

[T. Valko]

By the way I did try the code you first gave me in validation

=IF(SUMPRODUCT(--(ISNA(MATCH(J3:J10000,Z1:Z10,0)))),"Alert","")

i placed colors in cells Z1 to Z10 and when i entered a correct color it said it is not valid.


Gibs

That's because it requires that EVERY cell in the range J3:J10000 must contain one of the keywords. If ANY cell in the range does not contain a keyword then you get the "Alert".

That's how I interpreted what you wanted to do.

I see you want a VBA solution for this but I'm not the right person for that so I'll bow out.

 

[gibsongk55]

Not a clean one but I tink it works...

Excel Workbook
	S	T	U	V
1	List		Input
2	Brown		Purple	Not on the List
3	Blue		Blue
4	Green		Lavnder	Not on the List
5	Red
6	Black
Sheet2

Hi Snoop,

Tried that and i get the same thing invalid when it is in the list.


Thanks,

Gibs