Search code examples
excelexcel-formulaconditional-statementsrowcell

Need formula to help cross-check values and conditions in different columns

I am hoping for help for a new challenge in my veterinary research project. I would like a way to cross check values in different columns in my Excel spreadsheet. I want to make sure that for every case (bird) that has been assigned a category that it has at least one diagnosis (subcategory) for that category; and that no diagnoses were incorrectly assigned to a bird that didn't have that category assigned. I have 6500 cases (birds), 27 categories, and over 200 diagnoses (subcategories) and so I am hoping for a formula to help save me time and improve accuracy.

CATEGORY1 DIAGNOSIS1 DIAGNOSIS2 DIAGNOSIS3 CROSS-CHECK
BIRD1 N N N N 0
BIRD2 Y N N N 2
BIRD3 Y N N Y 1
BIRD4 Y Y Y N 1
BIRD5 N N Y N 3

So in the table above, there would be two CORRECT conditions in the CROSS-CHECK (output) column: "0" and "1".

  • "0" would be that the particular category was not assigned to the bird (CATEGORY1="N") and no diagnosis (subcategory) for that category was assigned (DIAGNOSIS1:DIAGNOSIS3="N").
  • "1" would be that a category was assigned (CATEGORY1="Y") and at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y").

There would be two ERROR conditions in the CROSS-CHECK (output) column, "2" and "3".

  • "2" would be that the category was assigned to the bird (CATEGORY1="Y"), but the bird was not assigned any diagnosis (subcategory) for that category (DIAGNOSIS1:DIAGNOSIS3="N")
  • "3" would be that the category was not assigned to the bird (CATEGORY1="N"), but at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y")

To add to the challenge, the "N" and "Y" in the CATEGORY and DIAGNOSIS columns have been populated using LAMBDA and BYROW functions.

I have tried several formulas, some including IF(COUNTIF), but can only come up with 2 potential condition (0/1, or Y/N) for the cross-check column. I need a more complex formula and I am having trouble writing it myself. Additionally, because of LAMBDA/BYROW I think, I have to do a hard return to each row in the cross-check column to get it to give the correct result. I do have Microsoft 365.

Solution

  • =BYROW(B2:E6,LAMBDA(b,LET(c,SUM(N(DROP(b,,1)="Y"))>0,IF(TAKE(b,,1)="Y",IF(c,1,2),IF(c,3,0)))))

    An if statement is used on the first column's value (IF(TAKE(b,,1)="Y"), next we check the count(/sum) of the remaining column's values equal to "Y" is greater than 0 (SUM(N(DROP(b,,1)="Y"))>0 declared as c) to get the differentiated results.

    Or a bit shorter: =BYROW(B2:E6,LAMBDA(b,LET(c,OR(DROP(b,,1)="Y"),IF(@T(b)="Y",IF(c,1,2),IF(c,3,0)))))