Forum Discussion

and_rogynous's avatar
and_rogynous
Copper Contributor
Apr 26, 2023

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...
excel
Formulas and Functions
office 365
HansVogelaar's avatar
HansVogelaar
MVP
Apr 28, 2023

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","")

  • and_rogynous's avatar
    and_rogynous
    Copper Contributor
    Apr 28, 2023
    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.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 28, 2023

      and_rogynous 

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

      • and_rogynous's avatar
        and_rogynous
        Copper Contributor
        May 01, 2023
        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?

Resources