Checking a word with 11 letters

[makinmomb]

Please Login or Register  to view this content.

[makinmomb]

Here is the attachment

[makinmomb]

What I am trying to achieve is for example

CLOSING is a word , the result is N because it does not contain 6 letters of what I am checking through a parameter of letters AEIOUDLNRST

[makinmomb]

For a word like TRAINEE the result is Y because
6 of these letters are in the check list against AEIOUDLNRST

For a word like MIRITIS the result to be true regardless the letter "I" appearing three times

[makinmomb]

3 non vowels and 3 vowels in any repeats allowed

Say the word BANANAS , result is yes because a is in the check list , n is in the check list and s too

[makinmomb]

In the file I have manually highlighted , red for those not matching and yellow those matching if someone would use
conditional instead of formula

[Pete_UK]

You can put this formula in B1 and copy down to give you the y/n that you showed earlier:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($C$1:$G$1,A1))))+SUMPRODUCT(--(ISNUMBER(SEARCH($C$2:$H$2,A1))))>=6,"y","n")

If you want to apply CF instead, select the cells A1 to A10 and choose them all to fill with red using normal formatting, then with the cells still highlighted click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box:

=SUMPRODUCT(--(ISNUMBER(SEARCH($C$1:$G$1,A1))))+SUMPRODUCT(--(ISNUMBER(SEARCH($C$2:$H$2,A1))))>=6

Click on the Format button, choose Fill and select Yellow. Then click on OK twice to exit the dialogue box and to apply the CFs. Note that conditional formatting has priority over normal formatting, so any cells that meet the condition will be coloured yellow.

Hope this helps.

Pete

[makinmomb]

Pete your formula almost correct

It knocks off word KHALIFA but does not knock N on CLOSING and FEATING

[Pete_UK]

Well it does for me - see attached file, which has both the formulae in column B and the CF applied.

Hope this helps.

Pete

[makinmomb]

Yes correct as needed

[makinmomb]

it marks the word BANANA red , works half way , cannot handle repeat letters

[makinmomb]

Works fine with unique letters , B is not on the check list but all letters AAANNS are part of the check list though appearing more than once

I will still use your solution for some other words but not for words like BANANAS

[Pete_UK]

You can use this formula in B1:

=IF(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,C$1,""),D$1,""),E$1,""),F$1,""),G$1,""),C$2,""),D$2,""),E$2,""),F$2,""),G$2,""),H$2,""))>=6,"y","n")

to cope with words like BANANAS.

Hope this helps.

Pete

[makinmomb]

This should do if you were kind to send it as attach and update CF , thank you

[makinmomb]

I then close thread , if you are updating CF , please do for whole column since when I type or paste
then , shades are taken care of

[Pete_UK]

Amended file attached.

Pete

[Pete_UK]

You hadn't posted #15 when I replied. If you want to apply to more cells, just select A10, click on Format Painter, then select the range of cells (from A11 downwards) that you want this to apply to.

Hope this helps.

Pete

[makinmomb]

Thank you gain Pete_UK