Find start shift excluding exceptions

[Uzma Shaheen]

Hey

I have a list of dates, agent, tasks, start and end time

What i need to do is find the start shift for agents rach day however i need to exclude any tasks that appear in the exception task list

Ie if the task exception list has interview, then
I need to ensure that if this is the first task then ignore that as the first start shift and look at next shift

Date….agent…task….start….end
02/10…..joe…..open….08:00….10:00
02/10…..joe…break….10:00…..13:00
02/10…..ava…interview..08:00…09:00
02/10…..ava…open……09:00….10.00
03/10…..tim…open….08:00….08:15
03/10…..tim…lunch….08:15….10:00

Exception List
Appointment
Interview
Offline

So in example above start time should be

Joe - 08:00
Ava - 09:00
Tim - 08:00

Etc….

I hope someone can provide a robust formula

Really appreciate it

 

[Uzma Shaheen]

Bump

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this do what you want?

23 10 04.xlsm
	A	B	C	D	E
1	Date	agent	task	start	end
2	2-Oct	joe	open	8:00	10:00
3	2-Oct	joe	break	10:00	13:00
4	2-Oct	ava	interview	8:00	9:00
5	2-Oct	ava	open	9:00	10:00
6	3-Oct	tim	open	8:00	8:15
7	3-Oct	tim	lunch	8:15	10:00
8
9	Exception List
10	Appointment
11	Interview
12	Offline
13
14	Joe	8:00
15	Ava	9:00
16	Tim	8:00
Start

Cell Formulas
Range	Formula
B14:B16	B14	=INDEX(D:D,AGGREGATE(15,6,ROW(D$2:D$7)/((B$2:B$7=A14)*ISNA(MATCH(C$2:C$7,A$10:A$12,0))),1))

 

[Uzma Shaheen]

Thank you Peter that is great - i will amend acc details…

Ive ran into another scenario where I don’t know if i can use same logic to achieve this result….

I am trying to get the total number of times a customer has called after speaking to someone and was that person they last spoke to (Abandoned column with a No means they spoke to someone)

Hoping you can help me with a formula or best approach for this even if its VBA

For example (I have large data set with multiple dates) - a sample below

Date…Name…Abandoned…Number…Repfrmno
01/02……….…….Yes…….200……
01/02….Dave…….No…….200……
01/02……….…….Yes…….200…….
01/02….Bill. …….No……200……..
01/02….H…..…….No…….200
01/02……….…….Yes…….200
01/02….Dave…….No…….200
01/02….Shaun…….No…….200
01/02….Dave…….No…….200

In the example above - for the number 200 on 01/02 there were 2 records after the first No and the first person that record was against was Dave (therefore 2 additional calls were made after speaking to Dave)

Then there was a call that Bill handled (No)…
And additional call was made handled by H (therefore there was additional call after speaking to Bill)

Then there were 2 additional calls made after speaking to H

There was 1 additional call made after Dave

Another additional call made after speaking to Shaun

No further calls were made after speaking to Dave last


Example above
Number 200 called 9 times (initial call was abandoned)

There were 7 repeat calls that day after speaking to someone (Dave first person they spoke to - first answered call)

7 repeat breakdown

2 calls were made after speaking Dave
1 call after Bill
2 call after H
1 call after Dave
1 call after Shaun

Essentially i want to put a flag (total number) in an additional column where it totals the subsequent calls made after speaking to someone and the name of last person they spoke to in another column

Im trying to get the total number of calls made after an answered call and who was the last person they spoke to so i can pinpoint it was a repeat after speaking to the last person

So 7 repeat calls were made after speaking to someone from a total of 9 calls that day 77.77%

I hope this makes sense

 

[Peter_SSs]

I hope this makes sense

I'm not sure that I fully understand, but ...

• More chance of getting the best approach if you follow through with the first line in your last post.
• Better chance of getting more potential helpers if you provide the sample data and expected results with XL2BB so that we can easily copy for testing and also better see the layout of the original data and the expected results.
• Can there be more than one data in the first column? If so, how does that affect the expected results? Sample data and expected results that include multiple dates would help if that scenario is possible.

 

[Uzma Shaheen]

Sorry ive only got access to my phone but i will follow your steps

Hey yes there could be more several of dates where the customer has called back however in this scenario im just interested in calls that were made on same day

I suppose based on the fact that there will br multiple days i can have 2 scenarios

1 that looks at same day contacts only

1 that ignores date column and looks at all calls made from the min date (data will be sorted in date and contact number order) therefore look at first date and then all calls after

I apologise if im still not making sense

 

[Uzma Shaheen]

Im using the latest version of excel and 365

				Expected Results
Same Day
Date	Name	Contact Number	Abandoned	Last Agent Spoke To (Repeat Calls)	How Many Additional Calls Made		Expected Results - I don’t need table below as I can pivot these but this is what results should like like as a whole
01/01/2023		125421	YES
01/01/2023		125421	YES
01/01/2023	Andy	125421	NO				Date	Grouped	Repeat Calls after speaking to colleague
01/01/2023		125421	YES	ANDY	1		01/01/2023	ANDY	2
01/01/2023	JAMES	125421	NO	ANDY	1		02/01/2023	ANDY	2
01/01/2023		125421	YES	JAMES	1
01/01/2023		125421	YES	JAMES	1		01/01/2023	SAM	1
01/01/2023	H	125421	NO	JAMES	1		02/01/2023	SAM	2
01/01/2023		125421	YES	H	1
01/01/2023	SAM	125421	NO	H	1		01/01/2023	JAMES	3
01/01/2023	SAM	125421	NO	SAM	1		02/01/2023	JAMES	5
02/01/2023	Andy	500500	NO				01/01/2023	H	2
02/01/2023	Andy	500500	NO	Andy	1		02/01/2023	H	1
02/01/2023	SAM	500500	NO	Andy	1
02/01/2023	JAMES	500500	NO	SAM	1
02/01/2023		500500	YES	JAMES	1		Total Number of calls made	22
02/01/2023		500500	YES	JAMES	1		Repeat Calls From an answered Call	18
02/01/2023	H	500500	NO	JAMES	1		Additional calls generated out of calls received after speaking an agent	81.82%​
02/01/2023	JAMES	500500	NO	H	1
02/01/2023		500500	YES	JAMES	1
02/01/2023	SAM	500500	NO	JAMES	1
02/01/2023	SAM	500500	NO	SAM	1

 

[Uzma Shaheen]

This will be scenario 2 to compare everything from initial received date for that contact number

Ignore date (Only start date to compare from contact number from)				Expected Results
Date	Name	Contact Number	Abandoned	Last Agent Spoke To (Repeat Calls)	How Many Additional Calls Made
01/01/2023		125421	YES
01/01/2023		125421	YES
01/01/2023	Andy	125421	NO
01/01/2023		125421	YES	ANDY	1
01/01/2023	JAMES	125421	NO	ANDY	1
01/01/2023		125421	YES	JAMES	1
01/01/2023		125421	YES	JAMES	1
01/01/2023	H	125421	NO	JAMES	1
01/01/2023		125421	YES	H	1
01/01/2023	SAM	125421	NO	H	1
01/01/2023	SAM	125421	NO	SAM	1
02/01/2023	Andy	125421	NO	SAM	1
02/01/2023	JAMES	125421	NO	ANDY	1
02/01/2023	JAMES	125421	NO	JAMES	1
02/01/2023	H	125421	NO	JAMES	1
02/01/2023	H	125421	YES	H	1
02/01/2023		125421	NO	H	1
02/01/2023	H	125421	NO	H	1
02/01/2023	JAMES	125421	NO	H	1
02/01/2023		125421	YES	JAMES	1
02/01/2023	SAM	125421	NO	JAMES	1
02/01/2023	ANDY	125421	NO	SAM	1

 

[Uzma Shaheen]

Hey @Peter_SSs
Please let me know if im not clear enough and whether you need more info

 

[Peter_SSs]

I'm struggling to understand/provide a useful suggestion.

BTW, please remove the 'Mark as solution from post #5 as it clearly does not contain a solution to your thread.