Returning A Sum From Multiple Occurences

[cdwelch4]

I am trying to sum the total number of a certain part used on different jobs. The worksheet is set up as follows:

Assembly #1 # for Job #1 # for Job #2 # for Job #3
Part #2 2 3 5
Part #4 1 1 1
Part #8 3 2 1
Part #10 6 4 2
Part #15 5 1 3

Assembly #2
Part #2 2 2 2
Part #4 1 2 3
Part #6 5 1 8
Part #7 6 8 7

Then I would filter the parts to determine the unique parts. In this case I would create a list like the one below:

Part #2
Part #4
Part #6
Part #7
Part #8
Part #10
Part #15

At this point I need something that will take each unique part from this list and then come up with the total number of each part (adding all three jobs together).

I was imaging something with an if statement that if analyzed the range of assemblies (which could be 100 different assemblies) and determined the total number of parts across all the jobs (but only for unique entries) which would require it to sum multiple lines. I just can't for the life of me come up with code that would do this.

Thanks for any help.

[Leith Ross]

Hello cdwelch4,

The attached workbook has the macro below. A button on the worksheet runs the macro. The output list of part numbers and their required totals appears in column "J:K" on the same sheet.

Please Login or Register  to view this content.

[cdwelch4]

That is great. Thank you so much. Is there a way to show two columns before the "Total Req'd" column that show the total for each job? To where it shows the total for Job #1, total for Job #2 and then the "Total Req'd" for all jobs.

[Leith Ross]

Hello cdwelch4,

This macro is a augmented version of the previous one. This list the Part Number, Jobs, Job counts, and the Total for each job starting in column "J". The output list is now sorted from smallest to largest part number.

Here is the improved code...

Please Login or Register  to view this content.