On Tue, 5 Aug 2008 06:44:01 -0700, LiAD
wrote:

Sorry I have already asked this but the answer has been lost somewhere, or i
can't find it now.

I have a list of data which i want to match the words then calculate the
difference between the first and second of the list, then sum all the
differences, i.e

bill 10
ben 9
fred 8
june 7
bill 6
ben 5
bill 4
ben 3
june 2

so i want it to return bill 6 (10-6 + 6-4), ben 6, june 5.

Any ideas on how to match the words, search for the next match then find the
differnce and keep that going for a list of 200 items with 20 different
names, summing all the differences?

As I understand your description of the problem, it would be the same
to say that you are looking for the difference of the two values
corresponding to the first and the last occurence of a given name.
If that is correct you may try the following formula.

Assuming that your names are in the range A1:A200 and the numbers in
the range B1:B200, put the following in cell D1:

=INDEX(B$1:B$200,1000-MAX(((A$1:A$200)=C1)*(1000-ROW(A$1:A$200))))-INDEX(B$1:B$200,MAX((A$1:A$200=C1)*ROW(A$1:A$200)) )

Copy this formula down as far as needed and write your names, bill,
ben, june, etc in cells C1, C2, C3 etc.

Cells D1, D2, D3 etc will show the differences for the respective name

Hope this helps / Lars-Åke