How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask swmcdonnell Your Own Question

swmcdonnell
swmcdonnell, Microsoft Office Expert
Category: Microsoft Office
Satisfied Customers: 233
Experience:  Steve uses Office and trains others in how to use Word, Excel, PowerPoint and Access.
47680681
Type Your Microsoft Office Question Here...
swmcdonnell is online now
A new question is answered every 9 seconds

I work for a call center and I get a daily report with

Customer Question

Hi - I work for a call center and I get a daily report with multiple tabs.
In RingNoAnswer tab it has a list of every call that wasn't answered with: start time, end time, client, operator and how long the call rang for.
In ExitWithTimeout tab, it has a list of calls that exited the queue with: start time, end time and client.
What I need to do is match up each RingNoAnswer call with each ExitWithTimeout call and let me know who the client and operator was and how long the call rang for.
Are you able to help?
Thanks!
Dave
Submitted: 1 month ago.
Category: Microsoft Office
Expert:  Dustin replied 1 month ago.

Welcome to Justanswer.com, my name is Dustin.
I'm sorry to hear about the issue you are having, I will do my best to help you resolve it.

Expert:  Dustin replied 1 month ago.

Hi Dave, what format is the report you receive in.. Is it an XLS excel file? Or, what type of file is it and what program do you use to access and open the reports?

Customer: replied 1 month ago.
HI Dustin - I've attached the file. my number is ************
Expert:  Dustin replied 1 month ago.

Excellent, its an excel file.. That means we have a lot of comparison options / formulas we can use.

Now, I just need to understand exactly what you are trying to do. Please allow me about 15 minutes to open the file , update excel if needed. And cross reference the file to what you said you needed earlier in this question.

Dustin S

Customer: replied 1 month ago.
xls
Customer: replied 1 month ago.
is there a better way to chat than this method? skypechat? I'm *********** on skype
Customer: replied 1 month ago.
or do you have all the info you need?
Expert:  Dustin replied 1 month ago.

For the moment I just need some time to compare the worksheets.. And figure out how to implement what you wanted, or as close to what you wanted as possible. I may have some questions after Ive reviewed. If so, you will be notified of my replies at the email address you registered with Justanswer.

Expert:  Dustin replied 1 month ago.

Ok.. need to ask you some questions..

Focusing on RingNoAnswer tab. its possible to create a simple formula that accurately relects in seconds, how long the call rang for.

Now, if we needed to compare the same unanswered call with the Exit with timeout Tab.

The only data that matches and correlates on both tabs, is the Start time. And only some of the start times shown on the RingNoAnswer tab are showing on the Exit with timeout tab.

So it would be possible to make a formula to compare Tab RNA ( ring no answer ) With Tab EWT (exit with timeout. )

But only in those circumstances where the Start times on the Tab RBA actually show on the Tab EWT

I think speaking over the phone for a few minutes may be best.. That way I can explain what I see, and maybe with further information we can discuss what is possible.

Customer: replied 1 month ago.
ok. can you please give me a call on +1(###) ###-####
Expert:  Dustin replied 1 month ago.

I will be calling from a 757 number, assuming you are sharing a US number.

D.S

Customer: replied 1 month ago.
Hi Dustin - any udpate? was expecting to see this this morning?
Customer: replied 1 month ago.
OK I guess I'll be asking for a refund and going elsewhere
Customer: replied 1 month ago.
thanks for wasting my time
Customer: replied 1 month ago.
how do I make an official complaint about Dustin? I would like to leave the lowest rating possible and a comment on his reviews to warn potential clients about not wasting their time with him. Or do you not allow that kind of thing?
Expert:  swmcdonnell replied 1 month ago.

Hi there, my name is*****'m sorry about your experience with Dustin, and while I'm sure you can register a complaint with someone, I don't handle that, so instead, I'll give you the answer to your question.

Here's what you need to do to accomplish what you've described. I also attached your spreadsheet here with the work already done, so you can take the answers and look at it to help understand the steps. It's actually not very complicated.

1. Start with the RingNoAnswer tab.

2. Insert a column at the beginning of the RingNoAnswer tab. It will be a new Column A.

3. Click in cell A2 and type the following formula:

=CONCATENATE(C2,D2,TRIM(G2))

4. Copy the formula in A2 all the way down the column to A50.

5. Highlight cells A2 to A50 and choose Copy. Then choose Paste Special and select Values and Number Formats.

6. Go to the ExitWithTimeout tab.

7. Insert a new Column A, click on A2, and type the following formula:

=CONCATENATE(C2,D2,TRIM(G2))

8.. Copy that formula from A2 to the end (A94)

9. Highlight cells A2 to A94 and choose Copy. Then choose Paste Special and select Values and Number Formats.

10. Click in cell J2 (still on the ExitWithTimeout tab).

11. Enter the following formula:

=VLOOKUP(A16,RingNoAnswer!$A$2:$J$50, 8, FALSE)

12. Copy that formula from J2 to the end (J94).

If you see a name, it matched. If you see "#N/A", it could not find a corresponding record in the RNA tab to match the EWT tab. The matching was done based on the start date, start time and queue.

Please give this a try and let me know if it works for you. (Steps 5 and 9 probably aren't necessary, but since we're not doing it together they are a safeguard to make sure it works properly).

Expert:  swmcdonnell replied 1 month ago.

Hey there, I was just following up with you to see if my solution worked for you. If it did, can you please accept my answer or I don't get paid anything for helping you.If it didn't, chat me back and I'll get you the right solution. Thanks and have a great day!