Results 1 to 9 of 9

Thread: Dcount

  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [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. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry

    UK landlines begin 01 or 02 (possibly 03 now)

    mobile numbers begin 07

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

    Luke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks, Stuart!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thnk you for your help.
    Best Regards,

    Luke

Posting Permissions

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