Forum Discussion

Kralin's avatar
Kralin
Copper Contributor
Dec 07, 2023

Assistance with a formula please

Hello - I would be very grateful for help with a formula please. Please see sample dataset (image below sorry couldn't see how to attach it!) I need the formula to look at each individual ID and th...
excel
Formulas and Functions
LouisDeconinck's avatar
LouisDeconinck
Copper Contributor
Dec 07, 2023

Kralin The following formula should correctly set the alert flag based on the attendance. Took quite a while to come up with, I recorded a custom video on how to build and implement this formula: https://www.youtube.com/watch?v=jBog2DTuAIg

 

 

=LET(a,$A$2:$A$21=A3,
b,FILTER($B$2:$B$21,a),
c,SORTBY(FILTER($C$2:$C$21,a),b,-1),
d,INDEX(c,1),
e,INDEX(c,2),
IF(MAX(b)=B3,IF(AND(OR(d="Did not attend",d="Cancelled"),
OR(e="Did not attend",e="Cancelled")),"Y","N"),""))

 

 

Optional tip if this was helpful: [link removed by admin] 

Resources