Filter, Advanced Filter or some other way

Lammer Law

New Member
Joined
Mar 23, 2018
Messages
2
I have been a user of this board for many years. Until now I have always found the answer I am looking for without ever having to join. I knew the day would eventually come when I couldn't find what I was looking and I'd have to join. And here I am. :)

I am attempting to do some analysis on call centre data. We've identified that we have a high repeat call rate but we've also identified that it is not across the whole operation. As the service we provide is confidential we do not record the details of individual callers, therefore the only way we can track repeats is by looking at the telephone number of those who call us. By extracting the the Calling Line Identity (or ANI as it is known in some markets) from our MI database I can see instances or where we have received 2 or more calls from the same telephone line.

The screenshot below shows what I can extract (it is completely anatomised in the screenshot). In this example I can see that ajones has had 3 occasions where they have spoken to someone who has then called back later. In the actual data the real person has significantly more than that!

MrExcel%201.PNG


I've then used VLOOKUP to count the number of times that ajones has appeared in the data and then calculated the percentage against their total volume. Again the data in the screenshot is only to show what I am doing and the actual numbers are way higher.

MrExcel%202.PNG


However, what it does not tell me is the split between when they were the first person to speak to the customer or the second, third, fourth, etc. In the example above you can see that in all 3 calls ajones has been the first person they spoke to. While we have customers who have called us multiple times in a few days I am not particularly interested in breaking that down as our main focus is trying to deal with the customer at the first point of contact.

In the full dataset ajones was involved in more than 100 calls where the same person has called more than once. Without going through it line by line I can't find a way of saying "of the 113 calls that ajones handled where the customer had called 2 or more times, on XX occasions ajones was the first person they spoke to.

I've tried filtering but cannot get that to work and would simply like to have a number alongside the total percentage.

Any suggestions would be more than welcome.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Not sure what I did wrong in my post but the image links don't appear to have worked. I've added them below.

MrExcel%201.PNG


MrExcel%202.PNG
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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