Pulsar3000
New Member
- Joined
- Apr 19, 2021
- Messages
- 44
- 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