How do i find a sequence

[LiAD]

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?

 

[John C]

Look at your post here, click on your name, this should open up a box. Then
click on Recent Posts by this user. That will bring up your other posts.

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

Forgot to mention that the formula is an array formula that has to be
entered with CTRL+SHIFT+ENTER rather than just ENTER.

Lars-Åke

 

[LiAD]

Tried but doesn't work for some reason, sorry.

Any other ideas of how to get a find a word string and calculate the
difference between two values associated with these words?

 

[Lars-Åke Aspelin]

"doesn't work" is not very helpful.
What result did you get? What problems did you encounter?

 

[John C]

I guess my question is are you really wanting the difference of the highest
value minus the lowest value? What if there are intermediary values?
In your example, you give 3 values of Fred, 67, 23, and 10, and state a
difference of 57. This is actually the high minus the low, which can be
portrayed easily in the array** formula of:
=MAX(IF(A$1:A$100=C12,B$1:B$100))-MIN(IF($A$1:$A$100=C12,$B$1:$B$100))
Where C12 is who you are looking for the difference for (such as Fred).

However, if you want the overall difference of all the numbers, then in your
example, it would be 114 for Fred, as 67-23=44, 67-10=57, 23-10=13, and
subsequently 44+57+13 = 114

 

[LiAD]

sorry i get a list of zero's for all of the names.

Maybe it will be easier if it was simplified into just making a list of all
the differences for each person rather than summing them all up. The rest of
the operations i can do after.

thanks

 

[Lars-Åke Aspelin]

You will get zero's in the D column if you don't have anything in the
corresponding cell in the C column.
Please make sure that you have written bill in C1, ben in C2, june in
C3 and so on.

Lars-Åke

 

[LiAD]

Yes I want all the values in between as well not just the highest minus the
lowest. At the end I would like a list of all of all the differences between
sequential Fred and Bill etc values that I can then play with to get max's,
avs etc.

Sorry I know my descriptions aren't clear maybe - trouble is i tried to
simplify the example as my data is a lot more complicated and not possible to
write in this.

In summary I would like excel to look through the list of names find two
names that match and return the difference between the two values associated
with these names, complete that for the full list of 200 values. From this
list I can then juggle whatever way I want, for now I do not how to ask excel
to find two equal text strings, find the values associated with these text
strings and perform a calculation on these two values, returning the value to
a specified cell.

Does this help or confuse?

 

[Lars-Åke Aspelin]

In your original post you gave the following example of input and
output

<qoute>

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.

<end quote>

My interpretation of this was that the parenthesis was just an
information to us how the result 6 for bill was obtained from the
input, not that "10-6 + 6-4" should be part of the output.
If the "10-6 + 6-4" should be part of the output, why did you not
state "9-3" for the ben output and "7-2" for the june output??

If my interpretation is correct, i.e. the stuff within the parenthesis
is just an explanation and not part of the output, then all
intermediate values for a person, the value 6 for bill in your
example, will net out to zero as it will be included in two
differences, and the final result will be the first value minus the
last value. If that is not what you want, maybe the example is could
be better choosen.

Now you say that you want "a list of all the differences..."
Where do you want that list? In one or several cells?

Could you please state exactly how you want the information to look
like for the given example above, or for any better example.
In which cells are the input?
In which cell(s) do you expect the output?

Lars-Åke

 

[LiAD]

Ok heres the proper stuff

Column L the text list I want to match - Bill, Ben, Fred (187 options in
total)
In column G against each line of column L i have a list of dates and times
in the format of 15/8/2008 15:00
In column O i need the formula to return a number of hours, that is the
subtraction of the first (a) time and the next (a+1) time value such as the
following;

Col L Col G Col O (time to
repeat of name)
Bill 15/8/2008 15:00 116 (time in
this row minus time in next bill row)
Ben 15/8/2008 17:00 124
Fred 20/8/2008 11:00 2
Bill 20/8/2008 11:00
Fred 20/8/2008 13:00
Ben 20/8/2008 19:00

and so it continues. So the function needs to

1 - look at the text string in the first row
2 - look down the list to find a match
3 - calculate the difference between the two times and return it adjacent to
the first occurance of the name
4 - continue down the entire list looking for matches and returning the time
differences

Does this help

 

[Lars-Åke Aspelin]

The 124 in your example should be 122 I think.

Assuming your data starts on row 1 and that the information in column
G is actually formatted time values rather than text, try the
following in cell O1:

=IF(MOD(COUNTIF(L$1:L1,L1),2),24*(INDEX(G2:G$187,MATCH(L1,L2:L$187,0))-G1),"")

Copy down to cells O2 to O187
Make sure to change the 187 if your data table increases.

This will give the time difference between the second and first
occurance of Bill on the same row as the first occurance of Bill,
the time difference between the forth and third occurance of Bill on
the same row as the third occurance of Bill and so on.
Column = will have blanks on the same rows as the second, fourth, and
so on, occurance of any name.

Hope this helps / Lars-Åke

 

[LiAD]

works perfect. Thanks very much for your help.

Another related question - whats the best approach if I want to then find the
max value in the list in column O for each person? i.e. excel looks at all
values for bill and return the max to column Q?

I promise thats the last

 

[LiAD]

cheers works well.

sorry been away on hols so not been on it. is it possible you could explain
to me how the last part of this formula works please? why the O&O1 and the
last bracketed parts?

i have tried to get this to do the same thing but for the minimums in the
next column across from the macimums but i can't get it. sorry for the
stupid questions.

Thanks

 

[Bernie Deitrick]

MIN works differently than MAX in array formulas, unless you are working with negative numbers,
because of all the 0 values returned.

Again, CTRL+SHIFT+ENTER rather than just ENTER.
=IF(COUNTIF(L$1:L1,L1)=1,MIN(IF(L$1:L$187=L1,O$1:O$187)),"")

HTH,
Bernie
MS Excel MVP

 

[LiAD]

sorry yet another question on this one.

excel is returning N/As for some fields because some of the names only have
single entries, for example if i add a last field called jane excel will
return an n/a rather than a blank cell. Excel trips up trying to find match
for something that has no match.

any ideas?

 

[LiAD]

just tried this sorry but it gets well tangled and completely bottoms out
when i try to ask it to trace the error. the cell says n/a.

i'm lost

 

[Bernie Deitrick]

I don't get any errors. Try shortening the ranges temporarily. Change

=IF(COUNTIF(L$1:L1,L1)=1,MIN(IF(L$1:L$187=L1,O$1:O$187)),"")

to

=IF(COUNTIF(L$1:L1,L1)=1,MIN(IF(L$1:L$5=L1,O$1:O$5)),"")

and then you can troubleshoot it better


HTH,
Bernie
MS Excel MVP

 

[LiAD]

thanks i got it to work was a formatting problem in the end. spreadsheets
almost too big to manage now.

just need to fix my problem with the errors appearing on the maximums now
when they can't find a next field.

thanks for your help