Adding Sum
 

I made a query report with unique records to display,I don't want repeating
item displayed in the report.
What should I do to sum the other qty delivered in a single item.

Item Qty Date
Pork 50 08/01/05
60 08/10/05
20 08/15/05

should be

Item Qty Date
Pork 130 08/01/05 - 08/15/05

thanks & Godspeed.

One way

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim dteMax As Date
Dim dteMin As Date
Dim nAmount As Double
Dim sCat As String
Dim iRow As Long

Columns("D:F").Insert
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
dteMax = Range("C2").Value
dteMin = Range("C2").Value
nAmount = Range("B2").Value
sCat = Range("A2").Value
iRow = 1
For i = 3 To iLastRow
If Cells(i, "A").Value = "" Then
nAmount = nAmount + Cells(i, "B").Value
If Cells(i, "C").Value dteMax Then
dteMax = Cells(i, "C").Value
ElseIf Cells(i, "C").Value < dteMin Then
dteMin = Cells(i, "C").Value
End If
Else
Cells(iRow, "D").Value = sCat
Cells(iRow, "E").Value = nAmount
Cells(iRow, "F") = Format(dteMin, "mm/dd/yy") & " - " & _
Format(dteMax, "mm/dd/yy")
sCat = Cells(i, "A").Value
nAmount = 0
dteMax = Cells(i, "C").Value
dteMin = Cells(i, "C").Value
iRow = iRow + 1
End If
Next i
Cells(iRow, "D").Value = sCat
Cells(iRow, "E").Value = nAmount
Cells(iRow, "F") = Format(dteMin, "mm/dd/yy") & " - " & _
Format(dteMax, "mm/dd/yy")

End Sub



--
HTH

Bob Phillips

"nono" wrote in message
...
I made a query report with unique records to display,I don't want
repeating
item displayed in the report.
What should I do to sum the other qty delivered in a single item.

Item Qty Date
Pork 50 08/01/05
60 08/10/05
20 08/15/05

should be

Item Qty Date
Pork 130 08/01/05 - 08/15/05

thanks & Godspeed.