Log In
Register
Home
April 23, 2015
Offline
Hi there,
In the attached workbook you can see I am extracting a data set on a daily basis with the date of the extract shown in column E. I am trying to figure out how I can compare the state shown in column B from one extract date to another for the value in column A i.e. when is does the name in column A change from state "DOWN" to "UP" or vice versa over the range of extract dates.
The idea is that on a day to day basis I can easily spot/filter out the instances of a change of state, (column B).
Hope that makes sense, and thanks in advance for the help!
Alan
Trusted Members
Moderators
November 1, 2018
Offline
You could use a formula like:
=IFERROR([@State]<>LOOKUP(2,1/([@Name]=$A$1:$A1),$B$1:$B1),FALSE)
which will return TRUE if the current state is not the same as the last entry above it for that Name. This assumes the data is sorted in date order as in your example.
April 23, 2015
Offline
Hi Velouria,
Many thanks for helping me out here. I added your formula but have done something wrong I guess as I can't get it working, without the "IFERROR" I am getting a DIV/0 error. In the attached file I have added your formula to column G. I changed the "State" value of row 1368 and was thinking that would result in a "TRUE" return in column G of row 1654 but I am not getting it.
Can you take a look and see what you think?
Thanks again,
Alan
Trusted Members
Moderators
November 1, 2018
Offline
You've got REF errors in that sample. Can you fix and re-post?
April 23, 2015
Offline
Hi Velouria,
Sorry about that - I changed the formula replacing the #REF errors with the cell references and it worked! Sample attached, many thanks for helping me with this one, much appreciated.
Alan
1 Guest(s)
