Forum Discussion
If Statement, But Only Wanting Result If Not Already Shown Above in Table
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?
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?
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","")
