1. Hi,

Using excel 2000 and currently doing a company mobile phone analysis.

I can download everyone's call patterns for a set month. I then copy this into Excel. It has the date of the call, number dialled, duration and cost of the call.

What I am interested is to count how many landline calls are made by a set user and their call duration.

I then need to filter out calls to others company mobiles and their duration

I then have the other numbers such as international (very few) and other mobile calls, which I need to total their duration.

I have never used excel in this way before, but this will enable me to make a more informed decision for switching our compny mobiles to another provider or revising our existing contract.

I have attached a sample with this post to highlight the type of data that gets pasted into excel.

Luke

2. Please forgive my ignorance, but how does one distinguish mobile numbers from landline numbers in the UK? And how does one distinguish in-company mobile numbers from other mobile numbers?

(I assume that column C contains the phone numbers, and that 121 represents internal calls)

3. [quote name='HansV' post='769597' date='07-Apr-2009 21:17']Please forgive my ignorance, but how does one distinguish mobile numbers from landline numbers in the UK? And how does one distinguish in-company mobile numbers from other mobile numbers?

(I assume that column C contains the phone numbers, and that 121 represents internal calls)[/quote]

Hans,

121 is the code for collecting voicemail for at least one of the UK mobile phone providers.
I think that all mobile numbers start 07

4. Sorry

UK landlines begin 01 or 02 (possibly 03 now)

mobile numbers begin 07

121 is the voicemail for vodafone (network we are on)

5. Thanks, Stuart!

6. Since you have stored the phone numbers as numbers, not as text, the leading zeros are missing.

You could add some columns with formulas and use a pivot table - see the attached version.

7. How could I bring that together with the following:

the last six digits of the company mobiles are 561145, 561143, 899495, 546447, 546446, 275644

How can I create a break down for that within the pivot table?

8. See the attached version - I added a small table with the last 6 digits of the company mobile numbers as text, and added another calculated column to the data table.

9. Thnk you for your help.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•