+ Reply to Thread
Results 1 to 11 of 11

Consolidate Number of Invoices

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Consolidate Number of Invoices

    Hello:

    Please refer to attached excel sheet for referance.

    Sheet1 has data which shows when invoice was generated and payment received.
    From here i have calculated the # of days between the invoice and payment.

    I need to know ( in Sheet2) how many invoices in 1-30 days, 31-60 days, etc in the table format as below:


    # of Invoices

    01 - 30 days
    31 - 60 days
    61 - 90 days
    91 - 120 days

    etc...


    511 - 540 days
    541 - 570 days
    571 - 600 days

    Please help and let me know if you have any questions..

    Thank you

    Riz Momin
    Attached Files Attached Files
    Last edited by rizmomin; 07-08-2010 at 04:49 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Consolidate Number of Invoices

    Possibly a Pivot table...

    Go to Data|Pivot Table..

    Click Next... it should select your whole range... verify it.

    Click Next and click Layout button. Drag Invoice ID to the Data Area and double click to change to Count (instead of Sum)

    Drag the DateDiff to the Row area.

    Click Ok. Select where you want the table to go.

    Click Finish

    Right click a cell in the DateDiff column and select Group and Show Detail and then Group...

    Starting at 1 and ending at 513 in step of 30.

    Click Ok.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Consolidate Number of Invoices

    I think i am missing some steps.....

    Please help ASAP

    Thank you

    Riz

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Consolidate Number of Invoices

    I couldn't attach yesterday, file too big after adding Pivot Table... I attached now but only 2000 rows worth.... to give you a picture. Follow the instructions...


    Here's an intro to Pivot Tables: http://peltiertech.com/Excel/Pivots/pivotstart.htm
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Consolidate Number of Invoices

    Hi :

    Sorry to bother u, but i am doing as per your instrution as above but i feel i am missing some steps..after ......

    Go to Data|Pivot Table..

    Click Next... it should select your whole range... verify it.

    Click Next and click Layout button. Drag Invoice ID to the Data Area and double click to change to Count (instead of Sum)

    Please check and let me know if i am doing something wrong...

    Thank u so much...

    Riz

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Consolidate Number of Invoices

    Are you in 2003 or 2007?

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Consolidate Number of Invoices

    i use MS Office 2003

    Thanks

    Riz

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Consolidate Number of Invoices

    So after you dragged the INVOICE ID button from the right side to the area labeled as "Data", then double click that new button that says "Sum of Invoice ID".. you should get a dialogue and you select Count and click Ok.

    Drag DateDiff button on the right to the area called "Row"

    Then click Ok.

    Select Existing Worksheet if you want the Pivot table in the same sheet.. click in the white box underneath and then click a cell where you want the table to go.

    if you want it to go in a new sheet, select New Worksheet.

    Click Finish

    Right click any cell in the DateDiff column of the Pivot Table you generated and select Group and Show Detail and then Group...

    In Starting field enter 1 and ending field enter 513 in step field enter 30.

    Click Ok.

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Consolidate Number of Invoices

    Yes i can only do upto 1st step..this is what i can

    Count of Invoice ID Total
    Total 36397

    Now i cannot see the box to drag DateDiff....

    So i can not go any further...

    Please check and let me know..
    Riz

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Consolidate Number of Invoices

    I think you went too far...

    Right click on that table you created and select Pivot Table Wizard.

    Then click Layout.

    Now drag the DateDiff over to Row section... and continue with the rest of the instructions.

  11. #11
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Consolidate Number of Invoices

    Hey i beleive i got it now....

    Thank u so much for this great help....

    Riz Momin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1