compare value with value from previous period for ID's

[Bozo the C]

Hello a good day,

I would like to compare the value from certain periods with de value from the previous period. As an example i have written the wanted result in the column on the right.
True, or false is enough. Besides the period there is also the ID as a criterium. If sorted i can use simply match, but i also wan't to make it work unsorted.

Could you please help me with the formula?

ID Date Value Does the value differ from previous period?
1 1-1-17 AA Error
1 3-1-17 AA False
1 6-1-17 BB True
2 1-1-17 AA Error
2 3-1-17 BB True
2 6-1-17 BB False
3 1-1-17 AA Error
4 6-1-17 AA Error
5 1-1-17 AA Error
5 3-1-17 BB False
6 6-1-17 AA Error
7 1-1-17 AA Error
7 3-1-17 AA False
8 1-1-17 AA Error
9 1-1-17 AA Error
9 3-1-17 AA False
9 6-1-17 BB True
10 1-1-17 AA Error
11 1-1-17 AA Error
12 1-1-17 AA Error
<link id="Main-File" rel="Main-File" href="../example.htm"><link rel="File-List" href="filelist.xml">

 

[Jonmo1]

I'm sure this is doable, but the logic doesn't make any sense to me.
Can you explain a little more detail how/why each row would be Error True or False ?

You mentioned you could do it if it was sorted, perhaps showing us how it would appear sorted would help us understand the logic.

 

[Bozo the C]

Thanks Jonmo, sorry, it's not very clear.

For cell D2 (the place of the first error) this would be:
IF(C2<>C1,"True","False")

This for the specific ID. And it also does not place an error if there is no previous period for the specific ID on that row.

Too hasty

 

[Bozo the C]

Perhaps it will make more sense if i explain what i want to do with it.

In stead of the "True", i can place a 1.

With a 1 for each new value, you can count the number of mutations.

 

[Bozo the C]

Alternative is counting the frequency

=SUM(IF(FREQUENCY(MATCH((C2:C21,C2:C21,0)*(A2:A21=A2),MATCH(C2:C21,C2:C21,0)*(A2:A21=A2))>0,1))-1

But i think checking for a change is more nice.

 

[Jonmo1]

Is it like this in D2 and filled down ?
=IF(A1<>A2,"Error",C1<>C2)


It seems in your example, the 11th row (the 2nd ID 5) you have backwards, it should be TRUE.

 

[Bozo the C]

Yes, you're right two times!

Row eleven is wrong.

Your formula seems perfect in case everyting is sorted.

Do you know how to make this work in case the table is unsorted?

 

[Jonmo1]

Try this in D2 and filled down

=IF(COUNTIF(A$2:A2,A2)=1,"Error",COUNTIFS(A$2:A2,A2,C$2:C2,C2)=1)

 

[Bozo the C]

Wow, this seems totally perfect!

Thank you very much for your help and the lesson!

 

[Jonmo1]

You're welcome.