Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract Unique and Sum (Excel 2003)

    Hi

    If Migration Batch = 1 Ineed to get totals from QS, I only need the code and description once, but the sum of how many are in Column K

    Likewise if Migration Batch = 2,3,4

    For Information there are 22,724 rows but only 118 codes

    I did a and advanced filter to get unique codes and descriptions but I can't do the bit above to tell me how many were sold of each unique code

    I hope this is clear.

    Many thanks braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Unique and Sum (Excel 2003)

    It sounds like you want a pivot table.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Unique and Sum (Excel 2003)

    Hi Rory

    Is it possible to do a pivot table with non adjacent cells, I am not very well up on Pivot Tables

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Unique and Sum (Excel 2003)

    Yes, though you may have to put in some dummy headings for the blank columns. I'm not entirely sure from your workbook which cells are original data and which are output - are the data cells columns A through V? If you post a spreadsheet with a bit more data, I can show you an example.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Unique and Sum (Excel 2003)

    Hi Rory

    I have attached some data from the acutal workbook so you have the correct layout

    I only need to see the the result as Item code 420670 Description GOOD SENSE FR. APPLE 2X6X20 ML Total in this case would be QS = 9


    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Unique and Sum (Excel 2003)

    Hi,
    See attached sample.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Unique and Sum (Excel 2003)

    HI Rory

    I lost the plot a bit, I fogot to say to total are to be based on the Migration Batch either 1,2,3,4

    ie total that match Batch 1 would probably be different to Batch 2 etc

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Unique and Sum (Excel 2003)

    How's this?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Unique and Sum (Excel 2003)

    Hi Rory

    Thats exactly what I am looking for, but I have no Idea how to apply it to my WorkBook!

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Unique and Sum (Excel 2003)

    Select a cell somewhere in your data then choose Data-Pivot Table and Pivot Chart Wizard. Choose Excel List or Database at step 1 and press Next; let it select the entire worksheet as data source in step 2 and press Next; then choose new worksheet in step 3 and press Finish. Then you should have a blank pivot table. Drag the Saleable Item and Item Description fields from the field list to the Row Fields area. Drag the Migration Batch field to the Column Fields area. Finally drag the 'QS (indirects) 6mth' field to the data area (it should default to sum).
    You may need to right-click on one of the SaleableItem entries, choose Field Options and then set subtotals to None.
    Any problems let me know.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Unique and Sum (Excel 2003)

    Hi Rory

    I am most grateful for your help.

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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