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

 

[joel]

I fully test this code so it should work if there is at least some dat
starting in row 4.

Sub Makesubtotals()
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Rows("4:" & LastRow).Sort _
header:=xlNo, _
key1:=Range("C4"), _
order1:=xlAscending

RowCount = 4
StartRow = RowCount 'first row of the addion for each product code
Do While Range("C" & RowCount) <> ""
If Range("C" & RowCount) <> _
Range("C" & (RowCount + 1)) Then
'insert new row
Rows(RowCount + 1).Insert
'make a formula to add the column
Range("N" & (RowCount + 1)).Formula = _
"=Sum(F" & StartRow & ":F" & RowCount & ")"
Range("O" & (RowCount + 1)).Formula = _
"=Sum(K" & StartRow & ":K" & RowCount & ")"
Range("P" & (RowCount + 1)).Formula = _
"=O" & (RowCount + 1) & "/N" & (RowCount + 1)

RowCount = RowCount + 2
StartRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Su

 

[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

 

[joel]

I'm thinking it something with the data and not the code since I ddi
good job of checking the code and have used this code lots of time i
the past.

Can you post the formulas that you have in cells N13, O13, P13. Th
formulas should read

=Sum(F4:F12)
=Sum(K4:K12)
=O13/O12

You first posting said you data starts in row 4. Your second postin
said you had 9 occurances of AGK (rows 4 to 12). the code would the
add an empty row at row 13 and insert the formulas in the new row.

The first row that starts counting is row for and the first row tha
get put into the formulas is the variable StartRow as shown in these tw
lines

RowCount = 4
StartRow = RowCoun

 

[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

 

[joel]

The data in C11 and C12 aren't exactly matching. this usually implie
the is either a extra space in the string or some letters ar
capitalized. The easy thing to do is to copy C11 to C12 and run th
code again

 

[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