Value in Cell once count = 2

P

Payroll

New Member
Joined
Aug 13, 2013
Messages
8
I am trying to find the duration of each break that an agent takes in a give day. The number of breaks is determined but I am not sure how to find each duration. Is there a formula the I could use that say when the count value = the break number that if give me the value in that column F in that row. The blue is what I am trying to create has there are over 600 employees and don't want to complete manually.

Date
Name
Aux Reason Number
Duration
24
 Breaks
1/5/2017
Ainsley, Amoy
24
0:14:53
Break #
1
2
3
4
1/5/2017
Ainsley, Amoy
24
0:14:17
Ainsley, Amoy
2
0:14:53
0:14:17
1/5/2017
Allen, Loran
24
0:14:32
Allen, Loran
2
0:14:32
0:14:19
1/5/2017
Allen, Loran
24
0:14:19
Allen, Shericka
3
0:12:03
0:16:05
0:14:57
1/5/2017
Allen, Shericka
24
0:12:03
Allen, Yhenoy
1
0:14:49
1/5/2017
Allen, Shericka
24
0:16:05
Allyson, Kemar
2
0:14:55
0:05:25
1/5/2017
Allen, Shericka
24
0:14:57
Anderson, Crystal
2
0:13:15
0:14:42
1/5/2017
Allen, Yhenoy
24
0:14:49
Anderson, Raymar
2
0:13:57
0:14:00
1/5/2017
Allyson, Kemar
24
0:14:55
Archer, Eric
4
0:13:24
0:13:01
0:14:49
0:14:50
1/5/2017
Allyson, Kemar
24
0:05:25
1/5/2017
Anderson, Crystal
24
0:13:15
1/5/2017
Anderson, Crystal
24
0:14:42
1/5/2017
Anderson, Raymar
24
0:13:57
1/5/2017
Anderson, Raymar
24
0:14:00
1/5/2017
Archer, Eric
24
0:13:24
1/5/2017
Archer, Eric
24
0:13:01
1/5/2017
Archer, Eric
24
0:14:49
1/5/2017
Archer, Eric
24
0:14:50

<tbody>
</tbody>

Thank you for your help!

Tabitha
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Assuming the names in column B are ordered as shown in your data sample; Data in columns A:D, maybe this...


F​
G​
H​
I​
J​
K​
1​
24​
Breaks​
2​
Break #​
1​
2​
3​
4​
3​
Ainsley, Amoy​
2​
00:14:53​
00:14:17​
4​
Allen, Loran​
2​
00:14:32​
00:14:19​
5​
Allen, Shericka​
3​
00:12:03​
00:16:05​
00:14:57​
6​
Allen, Yhenoy​
1​
00:14:49​
7​
Allyson, Kemar​
2​
00:14:55​
00:05:25​
8​
Anderson, Crystal​
2​
00:13:15​
00:14:42​
9​
Anderson, Raymar​
2​
00:13:57​
00:14:00​
10​
Archer, Eric​
4​
00:13:24​
00:13:01​
00:14:49​
00:14:50​

<tbody>
</tbody>


Create a list of unique names in column F (copy the list to F3:Fn; Select and go to Data > Remove Duplicates)

Formula in G3 copied down
=COUNTIF(B:B,F3)

Formula in H3 copied across and down
=IF(COLUMNS($H3:H3)>$G3,"",INDEX($D:$D,MATCH($F3,$B:$B,0)+H$2-1))

Format the cells accordingly

Hope this helps

M.
 
Upvote 0
Sorry the are not in that order. There are several different Aux Reason codes that are shown in time order of time the Aux Reason was selected. So I have to look for the Aux code 24 first. Could you help with that? I sort out the other Aux in the first example.

1/5/2017Ainsley, Amoy00:03:37
1/5/2017Ainsley, Amoy00:01:08
1/5/2017Ainsley, Amoy00:00:15
1/5/2017Ainsley, Amoy00:00:04
1/5/2017Ainsley, Amoy00:00:04
1/5/2017Ainsley, Amoy00:00:08
1/5/2017Ainsley, Amoy00:01:22
1/5/2017Ainsley, Amoy00:05:20
1/5/2017Ainsley, Amoy00:05:28
1/5/2017Ainsley, Amoy00:03:00
1/5/2017Ainsley, Amoy00:04:27
1/5/2017Ainsley, Amoy00:07:03
1/5/2017Ainsley, Amoy00:02:18
1/5/2017Ainsley, Amoy00:03:23
1/5/2017Ainsley, Amoy00:02:54
1/5/2017Ainsley, Amoy240:14:53
1/5/2017Ainsley, Amoy00:02:44
1/5/2017Ainsley, Amoy00:06:00
1/5/2017Ainsley, Amoy00:02:48
1/5/2017Ainsley, Amoy00:00:43
1/5/2017Ainsley, Amoy00:04:40
1/5/2017Ainsley, Amoy00:36:34
1/5/2017Ainsley, Amoy20:30:08
1/5/2017Ainsley, Amoy00:01:07
1/5/2017Ainsley, Amoy00:00:31
1/5/2017Ainsley, Amoy00:00:10
1/5/2017Ainsley, Amoy00:04:26
1/5/2017Ainsley, Amoy00:01:48
1/5/2017Ainsley, Amoy00:01:40
1/5/2017Ainsley, Amoy00:03:13
1/5/2017Ainsley, Amoy60:14:47
1/5/2017Ainsley, Amoy240:14:17
1/5/2017Ainsley, Amoy00:03:02
1/5/2017Ainsley, Amoy00:01:46
1/5/2017Ainsley, Amoy00:04:12

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

Thank you
 
Upvote 0
Are the names grouped, i.e., out of order but all occurences of a name are in contiguous rows?
If not, please post a data sample with different names out of order and not grouped.

M.
 
Last edited:
Upvote 0
The occurrences are always in time order for the same agent until the end of their shift then it will go to the next agent.

Here is what the report looks like when I receive. Then I create the table and remove all dupe names and look up ttl # of breaks and ttl # of Lunches and total time in Lunch. We were just using the total time in breaks but now I have to list the break times separate.

DateNameEmp #LogidAux Reason NumberAux Reason NameAux Start TimeAux Stop TimeDuration
1/5/2017Ainsley, Amoy275846005390928000:09:230:13:000:03:37
1/5/2017Ainsley, Amoy275846005390928000:13:170:14:250:01:08
1/5/2017Ainsley, Amoy275846005390928000:21:580:22:130:00:15
1/5/2017Ainsley, Amoy275846005390928001:06:501:06:540:00:04
1/5/2017Ainsley, Amoy275846005390928001:07:131:07:170:00:04
1/5/2017Ainsley, Amoy2758460053909280016:28:3716:28:450:00:08
1/5/2017Ainsley, Amoy2758460053909280016:30:1716:31:390:01:22
1/5/2017Ainsley, Amoy2758460053909280016:32:5716:38:170:05:20
1/5/2017Ainsley, Amoy2758460053909280016:38:2816:43:560:05:28
1/5/2017Ainsley, Amoy2758460053909280016:53:0416:56:040:03:00
1/5/2017Ainsley, Amoy2758460053909280017:03:4017:08:070:04:27
1/5/2017Ainsley, Amoy2758460053909280017:08:2317:15:260:07:03
1/5/2017Ainsley, Amoy2758460053909280017:29:1117:31:290:02:18
1/5/2017Ainsley, Amoy2758460053909280018:14:2818:17:510:03:23
1/5/2017Ainsley, Amoy2758460053909280018:18:4118:21:350:02:54
1/5/2017Ainsley, Amoy27584600539092824Personal18:32:2718:47:200:14:53
1/5/2017Ainsley, Amoy2758460053909280018:55:1718:58:010:02:44
1/5/2017Ainsley, Amoy2758460053909280019:13:0619:19:060:06:00
1/5/2017Ainsley, Amoy2758460053909280019:24:3719:27:250:02:48
1/5/2017Ainsley, Amoy2758460053909280019:35:5619:36:390:00:43
1/5/2017Ainsley, Amoy2758460053909280019:46:5919:51:390:04:40
1/5/2017Ainsley, Amoy2758460053909280019:54:2120:30:550:36:34
1/5/2017Ainsley, Amoy2758460053909282Lunch20:31:0921:01:170:30:08
1/5/2017Ainsley, Amoy2758460053909280021:05:4121:06:480:01:07
1/5/2017Ainsley, Amoy2758460053909280021:10:2221:10:530:00:31
1/5/2017Ainsley, Amoy2758460053909280021:23:5321:24:030:00:10
1/5/2017Ainsley, Amoy2758460053909280021:24:1521:28:410:04:26
1/5/2017Ainsley, Amoy2758460053909280021:47:2621:49:140:01:48
1/5/2017Ainsley, Amoy2758460053909280021:51:1721:52:570:01:40
1/5/2017Ainsley, Amoy2758460053909280022:18:4322:21:560:03:13
1/5/2017Ainsley, Amoy2758460053909286Team Mtg/Huddles22:46:2723:01:140:14:47
1/5/2017Ainsley, Amoy27584600539092824Personal23:01:1423:15:310:14:17
1/5/2017Ainsley, Amoy2758460053909280023:19:4223:22:440:03:02
1/5/2017Ainsley, Amoy2758460053909280023:22:5523:24:410:01:46
1/5/2017Ainsley, Amoy2758460053909280023:37:0023:41:120:04:12
1/5/2017Allen, Loran3899200053905980010:59:2411:00:080:00:44
1/5/2017Allen, Loran3899200053905980012:29:5612:31:280:01:32
1/5/2017Allen, Loran3899200053905980012:46:1512:46:570:00:42
1/5/2017Allen, Loran3899200053905980012:56:0412:59:380:03:34
1/5/2017Allen, Loran3899200053905980013:01:1313:02:520:01:39
1/5/2017Allen, Loran3899200053905980013:05:2913:06:050:00:36

<tbody>
</tbody>


This is what I used to get for the report
NameBreakBreak timeLunchLunch time
Ainsley, Amoy20:29:1010:30:08
Allen, Loran20:28:5110:29:48
Allen, Shericka20:28:0810:27:55
Allen, Yhenoy20:29:4610:29:57
Allyson, Kemar20:20:2010:32:45
Anderson, Crystal20:27:5710:30:18
Anderson, Raymar20:27:5710:29:11
Anderson, Yanique00:00:0000:00:00
Ankle, Chadwick00:00:0000:00:00
Archer, Eric20:26:2510:25:18
Armstrong, Jodian20:29:3910:29:48
Armstrong, Tacia20:27:5410:29:49
Bagaloo, Colarado20:27:1810:21:48
Barnaby, Simone20:29:5010:29:06
Barnes, DeAnna20:29:1210:34:37
Barnett, Tiffanie20:26:5810:29:33
Barrett, Danica10:13:5810:28:37
Bartley, Camille20:29:3210:29:13
Barton, Georgeta20:29:3410:30:00
Bauld, Anaray10:14:3810:30:02
Beckford, Diantha10:13:5210:28:36

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

Thanks again for your help!

<tbody>
</tbody><colgroup><col span="2"><col><col><col><col><col><col><col></colgroup>
 
Upvote 0
Bedtime to me. Tomorrow, if possible, i'll try to help.

Maybe someone else can help you.

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A
P
  • Solved
Transpose with dates problem - from the dd/mm/yy to mm/dd/yyyy
Replies
4
Views
405
Alex Blakenburg
A
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
180
harzer
H
V
  • Question
Hours functions
Replies
4
Views
339
jkpieterse
jkpieterse

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

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