Multiple Values in Single Cell: How can I apply formulas?

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. 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?

1619242637870.png


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
 

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Try first row formula and drag it down.
Book1.xlsm
NSTYZAAABAC
1
25070, 25, 55252
36030, 35, 40302
4200150, 100, 1201002
5120100, 90, 80801.5
6
Sheet3
Cell Formulas
RangeFormula
Z2:Z5Z2=IF(ISNUMBER(FIND(N2/2,T2)),N2/2,IF(ISNUMBER(FIND(N2/1.5,T2)),N2/1.5,""))
AA2:AA5AA2=N2/Z2

@maabadi

Thanks for you assistance maabadi! I really appreciate you lending a hand.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,189
Members
418,288
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top