Differing Criteria Formula

[Pumpkin]

Hey guys,

I need your wisdom as this is driving me mental:

I need to compile a list of results for supplier categories. There are only
4 Categories A,B,C,D.
Column D can have multiple results of a mixture of A,B,C,D.
What I need is to get a result in column E that is for the following
criteria if the cells in Column D have any of the (and could be a mixture)
following results:
A=A
AB=A
ABC=A
ABCD=A
BC=A
BD=B
CD=C
BCD=A

So for example if a supplier had 6 cells next to it containg C,D,B,D,C,D
this would then be an A Result as from the six cells there are a B & C entry.

Bit messy I know.
Any thoughts would help.

Pumpkin

 

[Luke M]

Need further info as to what determines final results. For instance, in your
example you say that the result is A because it contains BC. But it also
contains CD and BD, so why does the BC combo win? If A result is higher
priority (meaing if BC is found, disregard other combinations), why even
bother with AB, AC, ABC, etc, as it appears an A alone = A result.

 

[Pumpkin]

The Criteria is supposed to give a highest rating for what data is entered,
so in the case of BC=A this would overwrite if there is also a C & a D in the
data.
As far as the A criteria goes, I need it to basically say if theres an A in
the data report A (but im unsure how this would be added into all the other
formula script) I suspect I need an IF formula or an AND formula.

Can you help?