Results 1 to 5 of 5

Thread: Excel 2007

  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi!

    I am attempting to count how many order numbers have been created by each Cost Centre on the attached document.

    I would greatly appreciate your comments.

    thanks
    BK
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you want to count the number of unique order numbers for each cost centre, or the total number?

    For example, there are 10 rows for cost centre AR.4M.13, but only 3 distinct order numbers: 5595, 5616 and 5790.
    Should the output for this cost centre be 10, or 3, or something else?

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800251' date='28-Oct-2009 09:23']Do you want to count the number of unique order numbers for each cost centre, or the total number?

    For example, there are 10 rows for cost centre AR.4M.13, but only 3 distinct order numbers: 5595, 5616 and 5790.
    Should the output for this cost centre be 10, or 3, or something else?[/quote]

    Yes, I would like to be able to show a count of distinct order numbers per cost centre.

    thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can do this in two steps:

    1. Use Advanced Filter with the "Copy to another location" and "Unique records only" options to create a list of unique order numbers for each cost centre.

    2. Create a pivot table based on the table created by Advanced Filter.

    See the attached version: [attachment=86221:sumbyordercode.xlsx]

    Otherwise, you'd have to create a macro.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800264' date='28-Oct-2009 10:41']You can do this in two steps:

    1. Use Advanced Filter with the "Copy to another location" and "Unique records only" options to create a list of unique order numbers for each cost centre.

    2. Create a pivot table based on the table created by Advanced Filter.

    See the attached version: [attachment=86221:sumbyordercode.xlsx]

    Otherwise, you'd have to create a macro.[/quote]

    Thanks HansV, This works really well! and is fairly easy to do.
    BK

Posting Permissions

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