Subtotals

[gary]

When the value in column A changes, I need the amount-
subtotals for each of the values in columns B and C.

(See sample spreadsheet below

For example, for "687223007-5", I need:

For "19-038": $ 388.89
For "68-1366": 30.36
For "68-1378": 3.51
For "68-1856": 12.72
For "68-2232": 332.91
For "68-4115": 353.20

(Keep in mind that, for the value in column A, the number
of entries in columns B and C may range from 1 to ???).

Gary

A B C D

687196004-9 19-002 2,335.30
687223007-5 68-1366 20.24
687223007-5 68-1378 2.34
687223007-5 68-1856 8.48
687223007-5 68-2232 223.68
687223007-5 68-4115 242.40
687223007-5 19-038 257.82
687223007-5 68-1366 10.12
687223007-5 68-1378 1.17
687223007-5 68-1856 4.24
687223007-5 68-2232 109.23
687223007-5 68-4115 110.80
687223007-5 19-038 131.07
687233016-4 68-2232 22.58
687233016-4 19-003 76.24
687233016-4 68-2232 27.72
687233016-4 19-003 78.24
687233016-4 68-2232 28.76
687233016-4 19-003 80.78
687252002-0 68-1366 31.38
687252002-0 68-1378 3.64
687252002-0 68-1856 8.48
687252002-0 68-2232 134.82
687252002-0 68-4115 129.12
687252002-0 19-003 465.10
687252002-0 68-1366 31.38
687252002-0 68-1378 3.64
687252002-0 68-1856 8.48
687252002-0 68-2232 139.80
687252002-0 68-4115 129.96
687252002-0 19-003 480.18
689151007-7 68-2683 60.00
689151007-7 68-2694 10.78
689151007-7 68-2695 26.58
689151007-7 17-001 513.52
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 11.86
689151007-7 68-2695 30.56
689151007-7 17-001 401.74
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 12.86
689151007-7 68-2695 33.62
689151007-7 17-001 416.12
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 13.66
689151007-7 68-2695 40.46
689151007-7 17-001 424.04
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 13.66
689151007-7 68-2695 40.46
689151007-7 68-4612 9.90
689151007-7 17-001 438.38

 

[Peter Atherton]

Gary

If you arrange the data slightly you can use excel's built
in SubTotals function (Data Menu)

I have rearrange the data so that partnos? appear in one
column. There is a macro to do this if this arrangement is
suitable. The one following.

Sub MovePartNo()
Dim c As Variant
Dim i As Long, nr As Long
Dim rng As Range
nr = Application.WorksheetFunction.CountA(Range("A:A"))
Set rng = Range(Cells(2, 3), Cells(nr, 3))
' Copy column D parts to column C
For Each c In rng
If IsEmpty(c) Then
c.Value = c.Offset(0, 1)
End If
Next c
' delete column D
Columns("D").Delete shift:=xlToRight
End Sub

The following macro is just recorded it sorts the data by
job number (old column A) and then by Part number then it
creates subtotals each change in part# on price

Sub sortForSubTotals()
' Sort by Job then Part
'
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

' At each change in Part Sum Price
Selection.Subtotal GroupBy:=3, Function:=xlSum,
TotalList:=Array(4), _
Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Columns("C:C").EntireColumn.AutoFit
End Sub

This might have to be altered to run with more data - Im
not sure. The final data will look like this

Index Job Part no Price
1 687196004-9 19-002 2,335.30
19-002 Total 2,335.30
7 687223007-5 19-038 257.82
13 687223007-5 19-038 131.07
19-038 Total 388.89
2 687223007-5 68-1366 20.24
8 687223007-5 68-1366 10.12
68-1366 Total 30.36

This can be further condensed to this

Index Job Part no Price
19-002 Total 2,335.30
19-038 Total 388.89
68-1366 Total 30.36

If this is unsuitable someone will propably write another
macro to suit you

Regards
Peter