alphanumeric code match based on multiple criteria

Hi,

I am trying to find a formula(s) that help inform whether an alphanumeric code in a specific cell matches criteria for it to a be ‘Pass’ or ‘No Pass’ with other cells of the same column.

The 1st column would hold the alphanumeric code and the second column would hold the formula which would tell whether the code is a ‘Pass’ or ‘No Pass’.

An example of the codes used:

A1000
B1000
A2000
X2000
B300
Y300
A4000
Z4000
X4000
A800
B9000
X500
Z500

Explanation of how codes interact:

A and B codes can be standalone codes only (e.g. A800 or B9000) = Pass
X, Y and Z codes need to have a matching A or B code (e.g. A2000 and X2000 or B300 and Y300) = Pass
A and B codes can never match (e.g A1000 and B1000) = No Pass
X, Y and Z codes can never match (e.g X500 and Z500) = No Pass
Only one pair of A and B codes can ever match with X, Y and Z codes (A2000 and X2000 or B300 or Y300) = Pass
There cannot be more than one pair of either X, Y or Z codes that match with either an A or B code (e.g. X4000 and Y4000 and A4000) = No pass

The minimum number of digits for numbers used in a code would be 3 while the maximum would be 4.

I hope this makes sense and hope someone can help me out.

Thanks.

Have now attached an excel spreadsheet if this helps anyone?

Thanks for getting back to me.

The formula is not where I want it to be - just yet. I definitely think your on the right path though - thanks for your help so far.

I have attached an updated spreadsheet which I hope can help you out.

I have highlighted in orange where the formula is not working as expected.

I dont think the formula is taking into consideration the any Z codes?

The A4000, Z4000 and X4000 codes should all be 'No Pass' - this is because there are Z and X codes that match with A codes.

A5777, Y5777, X5777 and B777 codes should also be 'No Pass' also.

This is because there are Y and X codes that match, as well A and B codes that match.

If one does not pass they all should be 'No Pass'.

A777 and Z777 should 'Pass' because the code has a matching A code.

I hope this is a better explanation, I know there are a couple if things I did not mention before that I should have done.