multiple entries of same product sold. how to combine?

[bromanct]

I have two worksheets, "Sales" and "Returns"

On both, there are multiple entries for the same product sold (ie, product A is listed two different times, the first entry it shows 4 units sold, the second shows 3 units sold.)

How do I function/script it so I combine the two entries to one? The whole worksheet has many of these types of entries, all sorted by Product Code.

EXAMPLE<TABLE BORDER="1" CELLPADDING="0" CELLSPACING="0" WIDTH="75%"><TR><TD WIDTH="33%">Product</TD><TD WIDTH="33%">Product Code</TD><TD WIDTH="34%">Units Sold</TD></TR><TR><TD WIDTH="33%">Pickled Eggs</TD><TD WIDTH="33%">PE23</TD><TD WIDTH="34%">4</TD></TR><TR><TD WIDTH="33%">Pickled Eggs</TD><TD WIDTH="33%">PE23</TD><TD WIDTH="34%">3</TD></TR></TABLE></P>



-TO-</P><TABLE BORDER="1" CELLPADDING="0" CELLSPACING="0" WIDTH="75%"><TR><TD WIDTH="33%" HEIGHT="12">Product</TD><TD WIDTH="33%" HEIGHT="12">Product Code</TD><TD WIDTH="34%" HEIGHT="12">Units Sold</TD></TR><TR><TD WIDTH="33%">Pickled Eggs</TD><TD WIDTH="33%">PE23</TD><TD WIDTH="34%">7</TD></TR></TABLE>



help!?

_________________
monky-munk
This message was edited by bromanct on 2002-11-05 13:04
This message was edited by bromanct on 2002-11-05 13:09

 

[Yogi Anand]

Hi bromanct:

Welcome to the Board!

You have not stated where excactly are the Units Sold entries made -- Looking at the table in your posting, you could use the SUMIF function as...

=SUMIF(B2:B3,"PE23",C2:C3)
if your entries for ProductCode and UnitsSold were in cells B2:C3

If the data is in different worksheets and in different cells, you may have to bring the data over into one table.

Please post back if it works for you -- otherwise explain a little further and let us take it fom there.

Regards!
Yogi
This message was edited by Yogi Anand on 2002-11-05 13:31

 

[bromanct]

Yogi, thanks much for the reply.

The place where I want to display results is my TOTALS sheet, which simply shows the net from SALES minus RETURNS. On both the SALES and RETURNS sheets, the units sold/returned are all in the same columns. All information is hard data that has previously been entered.

I think I'll try your idea and also the Data -> Consolodate feature explained in topic 4691 by Mark W.

the main task at hand is automating this process for the 100+ multiple entries I'm dealing with. I figure that can be done with a loop - however my VBA is dusty and that challenge is next on my list. One step ar a time, right?