Insert rows to separate categories

Hi all,

I have a sheet of data which I need to separate into individual categories. They are all sorted by this category according to the value in column E. All I need is to work top-to-bottom through the spreadsheet and insert a row each time the value in column E changes. This way the data will all be the same but each category will be clumped together and different categories will be separate.

After this I need to get a SUM total of the values in column B for each category of data as clumped together by the above command. What makes this tricky is that the number of lines of data in each category is entirely variable - could be 1 row of data in any given category, could be 100...

Any pointers so appreciated
Thanks!
olly

Use the Data | Subtotals facility - it does exactly what you have described. On the dialogue box that it brings up you can specify "For each change in ...", and then you can specify which columns you want to sum (or average etc).

Hope this helps.

Pete

You could compare the value of cell E(Row) and E(Row+1) to find out when the value changes. When it changes you can then insert the row.
If the categories are then separate from each other by a blank row, the way to determine the number of rows is straightforward. Start at the first entry in column E (say row 1)

Activesheet.range("E1").Select
Selection.End(xlDown).Select
LastEntryRow = ActiveCell.Row

So the range for the first SUM is E1 to E & LastEntryRow
to Sum the range in cell B1 the vba syntax is


StartEntryRow = 1
activesheet.Range("B1") = "=SUM(E" & StartEntryRow & ":E" & LastEntryRow & ")"

The next category set now starts at LastEntryRow + 2 so

StartEntryRow = LastEntryRow + 2
and use the

Selection.End(xlDown).Select
LastEntryRow = ActiveCell.Row

to determine the new last row for the second category set.

To cycle through all the sets just use a For....Next loop.

The code could look like

Private Sub Separate_and_Sum()
'Inserts Rows based on changes to E values
For i = 1 To 100
FirstNumber = ActiveSheet.Range("E" & i)
SecondNumber = ActiveSheet.Range("E" & i + 1)
If SecondNumber = "" Then GoTo exitFN
If SecondNumber = FirstNumber Then GoTo 10
Rows(i + 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
10 Next i
exitFN:

StartEntryRow = 1

'Sums the Categories and puts the SUM into column B
For i = 1 To 100 '(or however mant categories you have
ActiveSheet.Range("E" & StartEntryRow).Select
Selection.End(xlDown).Select
LastEntryRow = ActiveCell.Row
ActiveSheet.Range("B" & i) = "=SUM(E" & StartEntryRow & ":E" & LastEntryRow & ")"
StartEntryRow = LastEntryRow + 2
If ActiveSheet.Range("E" & StartEntryRow) = "" Then GoTo ExitHere 'looks for blank value and stops if true
Next i

ExitHere:
End Sub