I have 2 lists of parts for the same job, one list is for one section of the
job the other is for another section, which helps determind parts allocation
during manufacture/assembly. However, i need to order the parts using the
same list (or a list derived from the original). The two lists are on the
same sheet i.e. (heading) Section 1 in cell A1 followed by section 1 parts
from A2 to A10 (for example), then (heading) Section 2 in cell A11 followed
by section 2 parts from A12 onwards. If a parts in section 1 is also in
section 2 i would like the part in section 1 to disappear and the quantity of
the part i section 1 to be added to the quantity of the part in section 2. So
if there were 5 off part 1's in section 1 and 6 off parts 1's in section 2,
then the final list would state 11 off part 1's. I guess i need excel to
check if a part in section 1 is in section 2, if not then leave it, if so
delete the part but add the number of parts to the quantity of the part it
matches in the other section. It seams quite simple but i'm a bit stuck, any
help would be much appreciated.

I have done similar with what I am working on. You can create this in one
workbook with 3 worksheets (tabs). Have the first two represent the two
different jobs, and the third will do calculations to sum the same line items
from the other two tabs. The formula is very easy to set up. just click on
hte cell you want to do the calculation in and type "=SUM(" then use the
cursor to click over to the first tab and click on the cell you want to add
first. Then you type in a comma "," and go to the next tab and cell you want
to reference and click on it. You can repeat with the comma between the
values until you have all that you want and then close the parenthesis ")"
and hit enter. This will add the items you want from the reference cells in
the tab you set up to order from.

"hot dogs" wrote:

I have 2 lists of parts for the same job, one list is for one section of the
job the other is for another section, which helps determind parts allocation
during manufacture/assembly. However, i need to order the parts using the
same list (or a list derived from the original). The two lists are on the
same sheet i.e. (heading) Section 1 in cell A1 followed by section 1 parts
from A2 to A10 (for example), then (heading) Section 2 in cell A11 followed
by section 2 parts from A12 onwards. If a parts in section 1 is also in
section 2 i would like the part in section 1 to disappear and the quantity of
the part i section 1 to be added to the quantity of the part in section 2. So
if there were 5 off part 1's in section 1 and 6 off parts 1's in section 2,
then the final list would state 11 off part 1's. I guess i need excel to
check if a part in section 1 is in section 2, if not then leave it, if so
delete the part but add the number of parts to the quantity of the part it
matches in the other section. It seams quite simple but i'm a bit stuck, any
help would be much appreciated.

On Apr 24, 3:20 pm, hot dogs
wrote:
I have 2 lists of parts for the same job, one list is for one section of the
job the other is for another section, which helps determind parts allocation
during manufacture/assembly. However, i need to order the parts using the
same list (or a list derived from the original). The two lists are on the
same sheet i.e. (heading) Section 1 in cell A1 followed by section 1 parts
from A2 to A10 (for example), then (heading) Section 2 in cell A11 followed
by section 2 parts from A12 onwards. If a parts in section 1 is also in
section 2 i would like the part in section 1 to disappear and the quantity of
the part i section 1 to be added to the quantity of the part in section 2. So
if there were 5 off part 1's in section 1 and 6 off parts 1's in section 2,
then the final list would state 11 off part 1's. I guess i need excel to
check if a part in section 1 is in section 2, if not then leave it, if so
delete the part but add the number of parts to the quantity of the part it
matches in the other section. It seams quite simple but i'm a bit stuck, any
help would be much appreciated.


Basically you need one column to combine the part ID's (column A:A)
and then you need SUMIF to add the parts of both lists. For the unique
parts list (temporarily remove the header separating the second list,
if it is in column A:A), pick a cell to start the list, say G2.

in G2:
=A2
In G3 (array formula, commit with Shift+Ctrl+Enter):
=INDEX($A$2:$A$20,MATCH(1,COUNTIF($G$2:G2,$A$2:$A$ 20)=0,0))

After copying down G3, you can now compute your sums in H2:Hn. In H2
(copy down):
=SUMIF(A:A,H2,C:C)

HTH
Kostis Vezerides