Totalling then Averaging subject to Product Code change

[Chris Maddogz]

I have a spreadsheet with the following data:

Sorted Alphabetic Product Codes (3-6 Alpha letters) in Column C starting at
Row 4 - the last entry in Column C will always be blank

Units (6 numeric numbers nnn,nnn format) in Column F also starting in Row 4

Value ($$$,$$$.cc format) in Column K also starting in Row 4

The main process occurs when a new Product Code occurs in Column C.

When it does I need to put in the last row of the the previous Product Code
the following:

1. total of the Units from its Column F into the the associated cell of
Column N

2. total of the Value from its Column K into the the associated cell of
Column O

3. divide the contents of the relative cell in O by the contents of the
relative cell in N to calculate the average into the relative cell in P
(fomat $$$.ccc).

For example when the rows in Column C change from code ABCD to ABCE
calculate the total of the units and value for ABCD and put their values into
the associated cells in column N and O of that last row for ABCD - then
divide the associated cell in O by the associated cell in N to get the
average and put it into associated cell in P.

If someone can help I would really like a macro to automate this process

 

[Chris Maddogz]

Thanks again Joel for that - still one small problem (example follows):

The first Product Code in Column C has the alpha letters AGK for 9 rows of
data starting at Row 4.

The next Product Code in Column C has the alpha letters AIO for 6 rows of
data starting at Row 13

When I run the code the first Row for Product Code (AGK) always gets moved
to the Row before the next Product Code AIO (ie Row 13)

Then the code totals rows 1 to 8 as a Product Code and Row 13 as a new
Product Code before moving on.

It seems like a problem only after its worked out how many rows there are in
the very first Product Code.

From then on everything is fine.

Thanks Chris

 

[Chris Maddogz]

Thanks joel for your patience here

The first code is called AGK (in C4) and despite there being 9 data rows for
it the first inserted Total line for AGK is in row 12 (not 13) and the
formulas in that line are as follows

N12=SUM(F4:F11)
O12=SUM(K4:K11)
P12=O12/N12

The only problem with the whole thing is that the original last data row for
AGK ( ie in row 12) gets moved down one row and treated as a new unique
Product Code with its own Total row inserted after it.

I stepped it through and watched it insert this last line then every Code
after that was fine and in the right place

 

[Chris Maddogz]

Thanks joel - I had a similar spreadsheet to this one and ran the macro fine
so I did as you suggested with a variation by dragging C4 all the way down
through to C11 and it ran fine.
Thanks again
Chris