Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    removing duplicates and summing (97 sr2)

    Hi all

    I have a list of part numbers and quantities that were counted. The items are in different locations and are on the list more than once.

    I need to subtotal the counts but still be able to use vlookup to compare the count to the amount in the accounting system. Vlookup is not working with the built in subtotal.

    I tried an advanced filter, but it would not add up the quantities for me.

    I have included three items, two of which need to be added.

    Any suggestions as to how to proceed?

    thanks

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

    Re: removing duplicates and summing (97 sr2)

    1. Copy the column headings (A1:B1) to another location, say A16:B16.
    2. Select a cell (any cell) in the data range.
    3. Select Data | Filter | Advanced Filter...
    4. The List Range will be selected automatically.
    5. Click in the Criteria Range box, and type or select A1:B1.
    6. Tick Copy to another location.
    7. Tick Unique records only.
    8. Click in the Copy To box, and type or select A16 (not A16:B16 !!)
    9. Click OK.
    10. The unique values from A2:A7 will be copied below A16.
    11. In B17, enter the following formula:<pre> =SUMIF($A$2:$A$7,A17,$B$2:$B$7)</pre>

    It is essential that the reference to A2:A7 and B2:B7 is absolute (witness the $'s) and that to A17 is relative.
    12. Copy down as far as needed.

    See attached workbook.

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: removing duplicates and summing (97 sr2)

    Hi Hans

    thanks for the assistance with this. That is working wonderfully.

Posting Permissions

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