Here is a code. It assumes that the original data has a header

Const target As String = "M1"
Dim iLastrow As Long
Dim iLastrow1 As Long
iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:A" & iLastrow)
.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range(target), _
Unique:=True
End With

iLastrow1 = Cells(Rows.Count, Range(target).Column).End(xlUp).Row
With Range(target)
.Offset(0, 1).Value = "Count"
.Offset(0, 2).Value = "Value"
With .Offset(1, 1)
.Formula = "=COUNTIF(A:A," & .Offset(0, -1).Address(False) & ")"
.Offset(0, 1).Formula = "=SUMPRODUCT(--(A1:A" & iLastrow & "=" &
_
.Offset(0, -1).Address(False) & " ),B1:B" &
iLastrow & ")"
.Resize(1, 2).AutoFill .Resize(iLastrow1 - 1, 2)
End With
End With


--
HTH

Bob Phillips

"abrogard" wrote in
message ...

I have a spreadsheet with a very long column containing many different
things that have been purchased in the past.

I need to discover how many different things have been purchased and
list them and find out how much has been spent on each of those
things.

So I need a method (a macro I suppose) of going down that column and
finding the first entry that hasn't already been dealt with - say
'screwdriver 6inch' and then continue down the column, find every
instance and take the cost (and date) and write it to some other part
of the spreadsheet.

When we finish I'll have columns for every different article. I can
look at what they are, know how many there are and see the total
expenditures.

I imagine this is the gist of what spreadsheets are for yet I can't
figure out how to do it automatically, not for reading all the help
screens I can't.

Perhaps it is too easy and there's couple of clicks will do it?

Please help if you know.

regards,
ab.
:)


--
abrogard
------------------------------------------------------------------------
abrogard's Profile:
http://www.excelforum.com/member.php...o&userid=24236
View this thread: http://www.excelforum.com/showthread...hreadid=378471