removing duplicates and summing (97 sr2)

Topic

New Reply

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

Reply | Quote

Replies

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:

  =SUMIF($A$2:$A$7,A17,$B$2:$B$7)

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.

Reply | Quote

Hi Hans

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

Reply | Quote

Hi Hans

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

Reply | Quote

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:

  =SUMIF($A$2:$A$7,A17,$B$2:$B$7)

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.

Reply | Quote