Find, maybe

C

chadlaw32

Board Regular
Joined
Jul 29, 2014
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I need to look up the sup total or Over time based on the name. Example i want to put Doe, John in a cell and have it bring me 0.77, any ideas?


Pay DatePay CodeHoursDollars
DoeJaneYG6001144
Sun 03/31/2019REGULAR-Regular0.27XXXX
Mon 04/01/2019REGULAR-Regular7.37XXXX
Tue 04/02/2019REGULAR-Regular8.28XXXX
Wed 04/03/2019REGULAR-Regular7.83XXXX
Thu 04/04/2019VACATION-VACATION8XXXX
Fri 04/05/2019REGULAR-Regular4.87XXXX
Subtotal36.62XXXX
DoeJohnYG6001457
Mon 04/01/2019REGULAR-Regular7.05XXXX
Tue 04/02/2019REGULAR-Regular7.65XXXX
Wed 04/03/2019REGULAR-Regular8.37XXXX
Thu 04/04/2019REGULAR-Regular9.35XXXX
Fri 04/05/2019OVERTIME-Overtime0.77XXXX
Fri 04/05/2019REGULAR-Regular7.58XXXX
Subtotal40.77XXXX

<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Try with this array formula:

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:115.2px;" /><col style="width:140.51px;" /><col style="width:79.42px;" /><col style="width:47.13px;" /><col style="width:49.75px;" /><col style="width:41.89px;" /><col style="width:90.76px;" /><col style="width:133.53px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Pay Date</td><td style="background-color:#ffff00; ">Pay Code</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Hours</td><td style="background-color:#ffff00; ">Dollars</td><td > </td><td style="background-color:#ffff00; ">Name</td><td style="background-color:#ffff00; ">OVERTIME-Overtime</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">Jane</td><td style="background-color:#92d050; ">YG6001144</td><td > </td><td > </td><td > </td><td >Doe, John</td><td style="text-align:right; ">0.77</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Sun 03/31/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">0.27</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">8.28</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.83</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Thu 04/04/2019</td><td >VACATION-VACATION</td><td > </td><td style="text-align:right; ">8</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">4.87</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">36.62</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">John</td><td style="background-color:#92d050; ">YG6001457</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.05</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.65</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">8.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Thu 04/04/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">9.35</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Fri 04/05/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">0.77</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.58</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">40.77</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >H2</td><td >{=OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0)+MATCH(H1,OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0),0,MATCH("subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0),0,1000),0)),0)-1,0)}</td></tr></table></td></tr></table> <br /><br />

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Upvote 0
Solution
That works great thank you very much!! What if they have 2 listings for Over time is there a way it can add the two numbers?

Sun 03/31/2019 REGULAR-Regular 7.05 XXXX
Mon 04/01/2019 REGULAR-Regular 8.60 XXXX
Tue 04/02/2019 REGULAR-Regular 8.62 XXXX
Wed 04/03/2019 REGULAR-Regular 7.90 XXXX
Thu 04/04/2019 OVERTIME-Overtime 0.97 XXXX
Thu 04/04/2019 REGULAR-Regular 7.83 XXXX
Fri 04/05/2019 OVERTIME-Overtime 7.82 XXXX

<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Looking at what I am doing I don't need the subtotal just a total for all lines over Overtime for each person if that helps.
 
Upvote 0
You could put an example with data of what you have and what you expect of result.
 
Upvote 0
Below is the example. It adds together the two or more of the OVERTIME-Overtime numbers under John Doe. I need it to add the overtime numbers because you could have over 40 hours and not be on over time so the subtotal does not work.

Last Name First NamePosition ID Doe, John7.05
Pay Date Pay Code Hours DollarsSmith, Frank4.15
Doe JohnYG6001163
Sun 03/31/2019 REGULAR-Regular 6.00 XXXX
Mon 04/01/2019 REGULAR-Regular 4.13 XXXX
Tue 04/02/2019 REGULAR-Regular 13.92 XXXX
Wed 04/03/2019 REGULAR-Regular 4.30 XXXX
Thu 04/04/2019 REGULAR-Regular 8.42 XXXX
Fri 04/05/2019 OVERTIME-Overtime 4.93 XXXX
Fri 04/05/2019 REGULAR-Regular 3.23 XXXX
Sat 04/06/2019 OVERTIME-Overtime 2.12 XXXX
Subtotal 47.05 XXXX
Smith FrankYG6001366
Mon 04/01/2019 REGULAR-Regular 8.50 XXXX
Tue 04/02/2019 REGULAR-Regular 10.15 XXXX
Wed 04/03/2019 REGULAR-Regular 8.50 XXXX
Thu 04/04/2019 REGULAR-Regular 8.50 XXXX
Fri 04/05/2019 OVERTIME-Overtime 4.15 XXXX
Fri 04/05/2019 REGULAR-Regular 4.35 XXXX
Subtotal 44.15 XXXX

<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Try this

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:101.7px;" /><col style="width:137.82px;" /><col style="width:67.49px;" /><col style="width:39.92px;" /><col style="width:41.82px;" /><col style="width:35.17px;" /><col style="width:76.04px;" /><col style="width:129.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Pay Date</td><td style="background-color:#ffff00; ">Pay Code</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Hours</td><td style="background-color:#ffff00; ">Dollars</td><td > </td><td style="background-color:#ffff00; ">Name</td><td style="background-color:#ffff00; ">OVERTIME-Overtime</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">David</td><td style="background-color:#92d050; ">YG6001144</td><td > </td><td > </td><td > </td><td >Doe, John</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">11.14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Sun 03/31/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">0.27</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Tue 04/02/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">8.28</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.83</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Thu 04/04/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">8</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">4.87</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">36.62</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">John</td><td style="background-color:#92d050; ">YG6001457</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.05</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.65</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wed 04/03/2019</td><td style="background-color:#ff0000; color:#ffffff; ">OVERTIME-Overtime</td><td style="background-color:#ff0000; color:#ffffff; "> </td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">8.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Thu 04/04/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">9.35</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Fri 04/05/2019</td><td style="background-color:#ff0000; color:#ffffff; ">OVERTIME-Overtime</td><td style="background-color:#ff0000; color:#ffffff; "> </td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">2.77</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.58</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">40.77</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >{=SUMIF(OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)),H1,OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)))}</td></tr></table></td></tr></table> <br /><br />
 
Upvote 0
If i don't need the subtotal match what can i remove.
 
Upvote 0
Then I did not understand, again, what data do you have and what are you going to put in name and what do you expect of result?
 
Upvote 0
Same data as above but i do not care about subtotals just that i get the total overtime as seen below. John Doe has two line of overtime totaling 9.14 hours.


ABCDEFGH
1Pay DatePay CodeHoursDollarsNameOVERTIME-Overtime
2DoeJaneYG6001144Doe, John9.14
3Sun 03/31/2019REGULAR-Regular0.27XXXX
4Mon 04/01/2019REGULAR-Regular7.37XXXX
5Tue 04/02/2019REGULAR-Regular8.28XXXX
6Wed 04/03/2019REGULAR-Regular7.83XXXX
7Thu 04/04/2019VACATION-VACATION8XXXX
8Fri 04/05/2019REGULAR-Regular4.87XXXX
9Subtotal36.62XXXX
10DoeJohnYG6001457
11Mon 04/01/2019REGULAR-Regular7.05XXXX
12Tue 04/02/2019REGULAR-Regular7.65XXXX
13Wed 04/03/2019OVERTIME-Overtime8.37XXXX
14Thu 04/04/2019REGULAR-Regular9.35XXXX
15Fri 04/05/2019OVERTIME-Overtime0.77XXXX
16Fri 04/05/2019REGULAR-Regular7.58XXXX
17Subtotal40.77XXXX

<tbody>
</tbody>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Improving my formulas/approach for date/time analysis
Replies
9
Views
918
mediumrare
M
Ironman
  • Solved
Msgbox when any one of a number of subtotals has been exceeded
8 9 10
Replies
92
Views
4K
Michael M
Michael M
L
  • Question
number based on the value of another column.
Replies
9
Views
762
Fluff
Fluff
Dreamteam
  • Question
Flipping values in excel to column titles
Replies
5
Views
690
MickG
M
J
  • Question
Question - Data extrat from one workbook into another
Replies
0
Views
486
John_Jay
J

Forum statistics

Threads
1,214,849
Messages
6,121,925
Members
449,056
Latest member
denissimo

Share this page

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
Back
Top