Hi Peter / Bob,

Thanks for all your help, sorry l did not reply earlier.

The code l eventually wrote which works within acceptable timescales
is copied below. I have used the column directly to the right of the
data as a helper column. Sorry if the commenting makes it difficult to
read. If you have any ideas for improving the code l am 'all ears'

Peter - I will try your code. It looks interesting and may be quicker
than mine and reduce workbook 'bloat' which is particularly important
in this workbook.

Once again many thanks.

Sub HelperFilterColumn()

Dim LR As Long 'Last row of loop
Dim sfr As Long 'Sum formula first row
Dim slr As Long 'Sum formula last row
Dim pn As Variant 'Personnel number to flag (ie put in
column J)

Application.ScreenUpdating = False

'Add subtotal to last row when value in column A changes
'-------------------------------------------------------
LR = Cells(Rows.Count, "A").End(xlUp).Row
sfr = 2
'....loop down insert subtotals
For i = 2 To LR
Cells(i, "A").Activate
'....when value changes i is equal to slr (sumif last row)
If Cells(i, "A").Value < Cells(i + 1, "A").Value Then
slr = i
Cells(i, "J").Formula = "=Round(SUMIF($A$" & sfr & ":$A$"
& slr & ",$A" & i & ",$G$" & sfr & ":$G$" & slr & "),2)"
'....i + 1 = sfr (sumif 1st row) sets 1st row of next
sumif
sfr = i + 1
End If
Next i

'Insert values included in subtotal that does not = 0
'----------------------------------------------------
pn = ""
'....loop up and find subtotals that are not 0
For i = LR To 2 Step -1
Cells(i, "A").Activate
'....if pn refers to a non 0 subtotal copy value to J
If Cells(i, "A").Value = pn Then
Cells(i, "J").Value = Cells(i, "G").Value
End If
'....if subtotal is not 0 get personnel number (pn)
If Cells(i, "J").Value 0 Or Cells(i, "J").Value < 0 Then
pn = Cells(i, "A").Value
End If
Next i
End Sub