Sylcarry87
New Member
- Joined
- Dec 10, 2015
- Messages
- 5
Hello. I have a master Bill Of Material sheet that I use to make BOMs, as options change for the order I delete sheets for those options not used. Each BOM will have far less data then the master, and some cells will change based on the needs of that particular job.
The BOM has multiple part numbers across sheets that are identical(part numbers). I cannot guarantee that the cells will be in the exact same place(row) for custom sheets I create. The quantity of each part number(cell to the left) changes as well, this is what I want to be summed up.
When I enter this data into our purchasing system I cannot create duplicate part numbers, thus I have to (tediously) find the previous entry and update the quantity. This is what I am trying to fix.
Here is some sample data: The first 3 columns are always stand alone columns with an item number I reference in drawings, the quantity that I want to sum up based upon, the part number that may or may not exist across multiple sheets. D and E are merged as well as f,g,h, and i. But these can be ignored!
[TABLE="width: 744"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]ITEM #[/TD]
[TD]QTY[/TD]
[TD]PART NO.[/TD]
[TD="colspan: 2"]MANUFACTURER[/TD]
[TD="colspan: 4"]DESCRIPTION[/TD]
[TD]ITEM[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]95[/TD]
[TD]3031720[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN TERMINAL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]141[/TD]
[TD]8[/TD]
[TD]3031733[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN GROUND TERMINAL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]142[/TD]
[TD]3[/TD]
[TD]3031762[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN TERMINAL END COVER[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]0.25[/TD]
[TD]3033710[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG JUMPER STRIP[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]144[/TD]
[TD]15[/TD]
[TD]0800886[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]TERMINAL END ANCHOR[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
If it is a matter of finding the duplicate part numbers and creating a "SUM" sheet that tallies these up I could be okay with that. My unicorn would be for excel to find duplicate part numbers without any input from me and total up the quantities tab for each duplicate found, in a "SUM" sheet, which will now exist in every BOM.
To summarize: I have part number 3031720 in sheets 1, 3, 4, and 7.
Part number will always be in column C. Quantity will always be in column B. But I don't know which row they will be in.
Excel finds part number in column C and takes quantity from column B and adds it up for all instances it finds, giving me a total for the entire workbook.
I could work with this scenario as well. I know most of the duplicates(and will add in new ones as I find them). I create a totals sheet with those part numbers, excel looks for these part numbers and takes the quantity from each instance so it can be summed up.
The BOM has multiple part numbers across sheets that are identical(part numbers). I cannot guarantee that the cells will be in the exact same place(row) for custom sheets I create. The quantity of each part number(cell to the left) changes as well, this is what I want to be summed up.
When I enter this data into our purchasing system I cannot create duplicate part numbers, thus I have to (tediously) find the previous entry and update the quantity. This is what I am trying to fix.
Here is some sample data: The first 3 columns are always stand alone columns with an item number I reference in drawings, the quantity that I want to sum up based upon, the part number that may or may not exist across multiple sheets. D and E are merged as well as f,g,h, and i. But these can be ignored!
[TABLE="width: 744"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]ITEM #[/TD]
[TD]QTY[/TD]
[TD]PART NO.[/TD]
[TD="colspan: 2"]MANUFACTURER[/TD]
[TD="colspan: 4"]DESCRIPTION[/TD]
[TD]ITEM[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]95[/TD]
[TD]3031720[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN TERMINAL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]141[/TD]
[TD]8[/TD]
[TD]3031733[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN GROUND TERMINAL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]142[/TD]
[TD]3[/TD]
[TD]3031762[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG STS 2.5-TWIN TERMINAL END COVER[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]0.25[/TD]
[TD]3033710[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]28-12AWG JUMPER STRIP[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]144[/TD]
[TD]15[/TD]
[TD]0800886[/TD]
[TD="colspan: 2"]PHOENX CONTCT[/TD]
[TD="colspan: 4"]TERMINAL END ANCHOR[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
If it is a matter of finding the duplicate part numbers and creating a "SUM" sheet that tallies these up I could be okay with that. My unicorn would be for excel to find duplicate part numbers without any input from me and total up the quantities tab for each duplicate found, in a "SUM" sheet, which will now exist in every BOM.
To summarize: I have part number 3031720 in sheets 1, 3, 4, and 7.
Part number will always be in column C. Quantity will always be in column B. But I don't know which row they will be in.
Excel finds part number in column C and takes quantity from column B and adds it up for all instances it finds, giving me a total for the entire workbook.
I could work with this scenario as well. I know most of the duplicates(and will add in new ones as I find them). I create a totals sheet with those part numbers, excel looks for these part numbers and takes the quantity from each instance so it can be summed up.