[Solved] Loop Through values until new value Appears, then Subtract

[sgillette01]

This problem should be easy, but it hasn't been for me. I am trying to get a macro to look at the value in A1 and compare it to B1. It the number after the decimal is different, I need it to subtract the number and put it in H1. If the number is the same then I need it to go to C1 and compare. Here is one of the kickers. The data goes A1, B1, C1, D1, E1 then A2, B2, C2, D2, E2 then A3, etc...

All the data after the decimal is either a .1 or a .6. So what I am really trying to do is hold a value in a variable that has a .1 until it finds a .6. When it finds the .6 it subtracts the .1 from the and gives me the time in H column. Then the variable holds onto the .6 value and continues looking for the next .1 value. Once found and subtracted, that number is place in the I column.

The numbers to the left of the decimal are seconds. I am trying to find out how long each event takes before switching to the next event. I hope this is clearer then I think it is!! Please see attached data sheet for an example. So far I have just counted it by hand.


Events.xlsx

[Domski]

I don't follow your example to be honest. Why is 183 the first value in the 0.6 column?

Dom

[sgillette01]

I thought it might be a little confusing. It should have been 182 not 183. The way I got it by finding the event that triggered the .6. This happened in B1. Then I look for the next time a .1 happens. It didnt happen in C1 or D1 or E1 or A2...but in B2 it happened. So I subtract 191-9 = 182. So I put 182 in Column I.

Now I have 191 and is a .1 event so now I am looking for the next .6 event. That happens to be in C2. So I subtract C2 from B2. Or 200-191 = 9. I then enter that in column H. And so on.

Hope that clears up the confusion

[sgillette01]

Can anyone help me with this?

[sgillette01]

Anyone??!?!!?

[dangelor]

Possibly...

Please Login or Register  to view this content.

[sgillette01]

Wow Dangelor, that works great! I wrote a macro to put everything in order in column g, so I changed your code a little bit to work off of that. One thing that is happening that I can't figure out. If you look at the attached spreadsheet I have color coded where the total is matching up with the numbers. I havent worked through all of it yet, but the second instance of the .1 says 1 sec. I can't figure out where that came from. It doesnt seem to make sense. Please let me know if I am just missing something?
Events-post run.xls

[dangelor]

Try it like this...

Please Login or Register  to view this content.

[sgillette01]

Thanks Dangelor! That worked great! I have never seen .item used like that before. Thanks again for helping out!

[dangelor]

You're welcome!