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