+ Reply to Thread
Results 1 to 9 of 9

Data summarizing!?

  1. #1
    Registered User
    Join Date
    04-17-2011
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    27

    Exclamation Data summarizing!?

    I use a software that generates reports in excel(These reports fill up Columns ("A:DG"). I have to clean up the reports every month and submit them. I am trying to make this process quicker.

    So I created a application that when activated removes all unwanted columns and sorts the corrects columns by either a Part number or a vendor (via userforum).

    I want to be able to summarize the information by the part number.

    So in Column ("A") I have a many of the same part number and in Column( "B") I have the quantity of that part number. Looks like this

    Part Number---------Quantity
    AAA123--------------------10
    AAA123--------------------25
    AAA123--------------------40
    AAA123--------------------30
    AAA123--------------------20
    AAA123--------------------75
    BBB345--------------------10
    BBB345--------------------25
    BBB345--------------------40
    BBB345--------------------30
    BBB345--------------------20
    BBB345--------------------75


    I want it to look like this:

    AAA123--------------------200
    BBB345--------------------200



    The generated report can have anywhere from 1-10,000 parts... Any help??
    Last edited by Crebsington; 05-27-2011 at 09:17 AM. Reason: Appearance

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Data summarizing!?

    I suspect a Pivot Table is the simplest solution.


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-17-2011
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Data summarizing!?

    Quote Originally Posted by TMShucks View Post
    I suspect a Pivot Table is the simplest solution.


    Regards


    A pivot table...

    I don’t mean to be a bother, but I don’t know what that is. Would you mind educating me?

  4. #4
    Registered User
    Join Date
    10-03-2009
    Location
    cairo
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Data summarizing!?

    search youtube about pivot table in excel , i believe you will find many

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Data summarizing!?

    Search the forum, there will be many examples
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Data summarizing!?

    See the attached example.

    In Excel 2007:
    select any cell in the data area
    select in the Insert tab/ribbon
    select the Pivot Table icon ... on the very left
    confirm the range and decide where you want the pivot table
    (in this case, I put it on the same sheet - cell Sheet1!$E$3
    Put Part Number in Row labels and Quantity in Values
    Close the dialogue box (x in the top right corner)

    Done

    Regards
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-17-2011
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Data summarizing!?

    Works Great, Thanks alot!

  8. #8
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Data summarizing!?

    If your problem has been solved please mark it as such.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

    Thank you
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Data summarizing!?

    You're welcome. Thanks for the rep.

    If this has answered your question, please mark your thread as solved. See previous post.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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