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

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

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

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

I imagine this is the gist of what spreadsheets are for yet I can'
figure out how to do it automatically, not for reading all the hel
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.
:

--
abrogar
-----------------------------------------------------------------------
abrogard's Profile: http://www.excelforum.com/member.php...fo&userid=2423
View this thread: http://www.excelforum.com/showthread.php?threadid=37847

I have a sheet that does this but I dont know how to e-mail it to you.
send me an e-mail and I will send the sheet to you.



--
ironmouse
------------------------------------------------------------------------
ironmouse's Profile: http://www.excelforum.com/member.php...fo&userid=9076
View this thread: http://www.excelforum.com/showthread...hreadid=378471

Simply select both the columns, i.e. item list and the cost list, go to
Data Pivot tables, and follow directions.

Mangesh


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

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