Pulsar3000
New Member
 Joined
 Apr 19, 2021
 Messages
 15
 Office Version

 365
 Platform

 Windows
Hello Excel Gurus!
Here's what's going on. I'm dealing with regular and overtime hourly rates.
The item after the bolded word function below yields results in a single cell as shown in the yellow cell of my image. Since multiple values are in a single cell, the cell is text. I know the Value function, multiplying by 1 or adding a 0 can fix the number saved as text to an individual number so some version of this I presume will be imbedded in the potential solution.
Question: Can the gold and blue box be accomplished strictly using some function(s) in cells Z2 and AA2 not involving VBA or Text to Columns?
Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))
Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded
Here's what's going on. I'm dealing with regular and overtime hourly rates.
The item after the bolded word function below yields results in a single cell as shown in the yellow cell of my image. Since multiple values are in a single cell, the cell is text. I know the Value function, multiplying by 1 or adding a 0 can fix the number saved as text to an individual number so some version of this I presume will be imbedded in the potential solution.
Question: Can the gold and blue box be accomplished strictly using some function(s) in cells Z2 and AA2 not involving VBA or Text to Columns?
Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))
Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded