Totalling then Averaging subject to Product Code change

  • Thread starter Thread starter Chris Maddogz
  • Start date Start date
C

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
 
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
 
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
 
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
 
Back
Top