Function Doesn't Work With New Data

[Pulsar3000]

When I remove the current raw data and replace it with new raw data, this function no longer works.

Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$7537=A2)*($C$2:$C$7537=C2)*($S$2:$S$7537=S2)*(@$T$2:$T$7537=T2)*($H$2:$H$7537<>0),$K$2:$K$7537,""))),0))

Question !: Why doesn't it work?

Question 2: Is there a better performing function? For example, I concatenated all the fields that it needs to match in helper Column X and changed the function to the below but it didn't work. Maybe I need to do an index match.

Alternative Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($X$2:$X$7537=X2),$K$2:$K$7537,""))),0))

 

[Pulsar3000]

When I remove the current raw data and replace it with new raw data, this function no longer works.

Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$7537=A2)*($C$2:$C$7537=C2)*($S$2:$S$7537=S2)*(@$T$2:$T$7537=T2)*($H$2:$H$7537<>0),$K$2:$K$7537,""))),0))

Question !: Why doesn't it work?

Question 2: Is there a better performing function? For example, I concatenated all the fields that it needs to match in helper Column X and changed the function to the below but it didn't work. Maybe I need to do an index match.

Alternative Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($X$2:$X$7537=X2),$K$2:$K$7537,""))),0))

Correction...the alternative function I meant is below...added the purple part.

Alternative Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($X$2:$X$7537=X2)*($H$2:$H$7537<>0),$K$2:$K$7537,""))),0))

 

[jasonb75]

Why doesn't it work?

Impossible to say without examples of the raw data to test the formula.

Personally, I would use this in an effort to reduce array size for some of the functions. Based on the first formula, not the alternative.

=IF(OR(W2={"OT Hours","Holiday Hours"}),TEXTJOIN(", ",TRUE,UNIQUE(FILTER($K$2:$K$7537,($A$2:$A$7537=A2)*($C$2:$C$7537=C2)*($S$2:$S$7537=S2)*(@$T$2:$T$7537=T2)*($H$2:$H$7537<>0),""))))

 

[Pulsar3000]

Impossible to say without examples of the raw data to test the formula.

Personally, I would use this in an effort to reduce array size for some of the functions. Based on the first formula, not the alternative.

=IF(OR(W2={"OT Hours","Holiday Hours"}),TEXTJOIN(", ",TRUE,UNIQUE(FILTER($K$2:$K$7537,($A$2:$A$7537=A2)*($C$2:$C$7537=C2)*($S$2:$S$7537=S2)*(@$T$2:$T$7537=T2)*($H$2:$H$7537<>0),""))))

@jasonb75 Thanks for the updated function! It definitely works.

 

[Pulsar3000]

When I remove the current raw data and replace it with new raw data, this function no longer works.

Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$7537=A2)*($C$2:$C$7537=C2)*($S$2:$S$7537=S2)*(@$T$2:$T$7537=T2)*($H$2:$H$7537<>0),$K$2:$K$7537,""))),0))

Question !: Why doesn't it work?

Question 2: Is there a better performing function? For example, I concatenated all the fields that it needs to match in helper Column X and changed the function to the below but it didn't work. Maybe I need to do an index match.

Alternative Function: (IF(OR(W2="OT Hours",W2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($X$2:$X$7537=X2),$K$2:$K$7537,""))),0))

I was able to discover my issue. Column S had some cells that resulted in #N/A. I wrapped the function in Column S inside of IFNA with an error text so that reviewers know to update a table. This fixed the issue.