filter and sum like part numbers

**vwdevotee** · 08-17-2010, 02:36 PM

I have a list of part numbers and sub assemblies like this:

PART_______________QUANTITY

ABC_______________1

123A_______________2

124A_______________4

125A_______________6

ABD_______________2

124A_______________3

125A_______________5

126A_______________7

I would like to flatten the list and sum all of the like part numbers into one line item like this:

PART_______________QUANTITY
ABC_______________1
ABD_______________2
123A_______________2
124A_______________7
125A_______________11
126A_______________13

I've been doing this by hand, but as my projects grow bigger I'm spending an hour or better just simplifying and summing the rows by hand. As part of simplifying this all I'm putting the simplified list on a seperate sheet. I'm not sure if it's possible, but I'd also like to keep the list dynamic so that I don't have to redo work as I make small changes on teh first list. Am I reaching too high for all this? Can anyone point me in the right direction on how to pull this off? Should I be looking at macros, or can it be done with built in functions? Thanks in advance!

**BigBas** · 08-17-2010, 02:44 PM

If no one else has any ideas, I can take a crack at it. Could you possibly post a sample of the workbook with some dummy data so I can get a better feel for the layout and . Also, if you choose to post a sample, please post in 2003 compatibility (if you have Excel 2007) because I do not have version 2007 on this compuer.

**vwdevotee** · 08-17-2010, 03:29 PM

Here's a demo data set. I've also done a second sheet in it showing more clearly what I'm trying to do. Obviously I did this the long way by manually reducing them. Thanks for the help.

**ChemistB** · 08-17-2010, 04:23 PM

Two methods.
1. On Bill of Materials Sheet insert a column A and in A3 =B3&C3 dragged down.
In row 2 of Simplified

=SUMIF('Bill of Materials'!$A$3:$A$180,A2&B2,'Bill of Materials'!$D$3:$D$180)

2. Without the dummy column, on simplified row 2,

=SUMPRODUCT(--('Bill of Materials'!$A$3:$A$180=A2), --('Bill of Materials'!$B$3:$B$180=B2),'Bill of Materials'!$C$3:$C$180)

Take your pick. Either of these work for you?

**vwdevotee** · 08-17-2010, 04:54 PM

That definitely takes care of the summing like part numbers part, but can it be modified to not create duplicate part number entries? If need be I can do that part by hand; this is still enormously helpful.

**martindwilson** · 08-17-2010, 07:19 PM

try a pivot table,unmerge cells and put a separate col heading add a new column as shown(i called it sum)

**vwdevotee** · 08-18-2010, 04:22 PM

Can you elaborate on how you configured the pivot table? That looks pretty much like what I was thinking.

**vwdevotee** · 08-18-2010, 04:23 PM

Also, can a pivot table dynamically update? I tried changing one of the values but it didn't update on the second sheet.

**martindwilson** · 08-18-2010, 04:44 PM

errr not of the top of my head! use the wizard and see whats hidden whats used
also check each fields properties and check the general properties.
you also need to right click refresh data id you add something new or even create a dynamic range for use in table