If Statement, But Only Wanting Result If Not Already Shown Above in Table

[and_rogynous]

I have a few tables pulling information about boat parts.

The table I'm currently working in has columns for order number [ON], boat number [B], customer part number [CPN], mark [M], our part number [PN], mold number [MN], and whether a piece is new [N]. Order number, boat number, and customer part number are filled out manually; the rest pull from other tables in the document.

The other relevant table is called OPN, which is the first order number in which our part number appears. The columns are order number [ON], customer part number [CPN], mark [M], and our part number [PN].

The "New" column has the following formula:

=IF(AND(COUNTIF(OPN[ON],[@ON])>0,COUNTIF(OPN[CPN],[@CPN])>0,"X","")

Currently, the cells remain blank unless the order number and our part number both match in the second table. However, there are a few instances where the same order number is on more than one table line (when one order has parts for multiple boats), and this leaves me with a predicament: all lines with that order number and our part number have an X in that column, instead of just one (which in these cases should be the lowest boat number).

How do I fix the formula?

[#2]

@and_rogynous 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@Hans Vogelaar 

[#4]

@and_rogynous 

Thanks! Use this formula in the N column:

=IF(AND(COUNTIFS(OPN[ON],[@ON],OPN[CPN],[@CPN])>0,MINIFS([B],[ON],[@ON],[CPN],[@CPN])=[@B]),"X","")

[#5]

This is very helpful, thank you! The only other question I have is - this makes the "X" that SHOULD be in this column for any order with "?" under the boat number (an older order that we don't have record of boat number) now return blank. Is there a way to adjust for that situation? "?" is the only non-number in the boat number column.

[#6]

@and_rogynous 

So what do you want to do with rows that have ? in the B column?

[#7]

The same sort of thing - the issue is that some of the order numbers that need an X correspond with a ? in the B column. I'm not sure why your fix for the formula made that go away, maybe because a ? counts as an invalid number?

[#8]

@and_rogynous 

Yes - MINIFS ignores all text values, including "?", so a cell with "?" will never equal the minimum value for an ON and CPN.

Perhaps with a helper column: