Forum Discussion

SuperCal99's avatar
SuperCal99
Helper I
5 years ago

Finding Ids across multiple columns

Hi,

 

I have been asked to compare if a specific id is present across a number of years - yes and no means they are present, blank means they are not present.

Below is an example of what i need - the data is within one table

 

Essentially i need the result in column e, stating whether the ID is present within each year column "yes", "no", and ignoring blanks.

 

Any help or additional questions needed please let me know

 

Kind regards,

 

Callum

 

 

    Result 
Abcde 
ID201920202021in 2021 and 2020 and 2019? 
000113 NoYesYes 
000114  YesNo 
000115  YesNo 
000116No YesYes 
000117  YesNo 

1 Reply

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Hi SuperCal99 ,
    You can create a calculated column using the IF condition as mentioned below:
    e = IF(AND(AND(b=" ",c="No"),d="Yes"),"Yes",
          IF(AND(AND(b=" ",c=" "),d="Yes"),"No",
          IF(AND(AND(b=" ",c=" "),d="Yes"),"No",
          IF(AND(AND(b="No ",c=" "),d="Yes"),"Yes",
          IF(AND(AND(b=" ",c="Yes"),d="No"),"No",blank())))))

    I hope this helps!

    SuperCal99 

    Did I answer your question? Mark my post as a solution!