lookup multiple times

[kgkev]

Good Morning,

i have two spread sheets (Database downloads)
One is supplier names (Suppliers)
One is Supplier contact details. (Contacts)

Each supplier appears once in the Sheet "suppliers" They have a unique identifier in Column A

Each supplier has multiple contact persons (or 1 or none).
The unique supplier code is stored in A
There is a contact type stored in F which is the data I need.
Each contact can be identified by 3 categories based on the communication they are set to receive.
They can be any combination of
P = Purchase Order
E = Expedite Report
R = Remittance Advice.

Smaller suppliers will have 1 contact who handles all communications
in this case Column F will be "PRE"

Larger suppliers with specific departments might have one contact for each category
Contact 1 = P
Contact 2 = R
Contact 3 = E

Also some suppliers will have multiple people set to any combination of contact types.

I am trying to identify any supplier that is missing either P, R or E contact types.
Happy to use three columns to check for P, R & E separately, but I cannot workout the logic.

Or is VBA the only way to go?

 

[Legacy 93538]

not sure if it helps but my suggestion would be in a new column in the suppliers sheet next to each record put a countifs function which would be for example"=COUNTIFS(L:L,A2,N:N,"p")+COUNTIFS(L:L,A2,N:N,"e")+COUNTIFS(L:L,A2,N:N,"r")" in this example L:L would be the supplierID in the contracts sheet and N:N would be the contracts type and A2 is the supplierid in the row. then if any are 0 then they have got no contracts with P,R or E

 

[kgkev]

Thanks,
This worked, but I had to break down my supplier type into three columns as well:

=IF(ISNUMBER(SEARCH(G$1,$F2)),G$1,"")

Very simple, thanks for the suggestion