Comparing multiple rows within multiple columns

[SteveNL86]

Dear reader,

I am trying to use an IF formula to give me a warning when the data of multiple rows in one column doesn't match with the data of multiple rows in another column.

To illustrate the issue, for example:
A B C
0,7 5 Ok
0,7 5 Ok
0,7 5 Ok
0,8 10 Ok
0,8 10 Ok
1 12 STOP
1 12 STOP
1 13 STOP
1,1 14 Ok
1,1 14 Ok
1,1 14 Ok

So the result for the groups 0,7, 0,8 and 1,1 are all the same, but 1 has a variation.

I'm not sure if I'm going at it right or all wrong. I was messing around with the following:
(C:C)=IF(AND(COUNTIF($A$2:$A$100;"=");COUNTIF($B$2:$B$B100;"="));"STOP";"Ok")

Any help is much appreciated!
Greetings

 

[Marcelo Branco]

What about 1.3?
13 is greater than 12 so i think it should be Stop, not Ok.
Very confuse!

M.

 

[SteveNL86]

Again your right. Sorry I overlooked that.

 

[Marcelo Branco]

Not sure i fully understand the logic.
I have to leave now - hope this does what you need.

	A​	B​	C​
1​	Value1​	Value2​	Result​
2​	0,7​	10,1​	Stop​
3​	0,7​	10,1​	Stop​
4​	0,8​	9​	Stop​
5​	0,8​	9​	Stop​
6​	1​	10,5​	Ok​
7​	1​	10,5​	Ok​
8​	1​	10,5​	Ok​
9​	1,2​	11​	Ok​
10​	1,2​	11​	Ok​
11​	1,3​	13​	Stop​
12​	1,3​	13​	Stop​
13​	1,5​	12​	Stop​
14​	1,5​	17​	Stop​
15​	1,6​	15​	Stop​
16​	1,6​	15​	Stop​

Formula in C2 copied down
=IF(OR(IFERROR(INDEX(B$2:B$100,MATCH(A2,A$2:A$100,0)-1)*ISNUMBER(B1),0)>INDEX(B$2:B$100,MATCH(A2,A$2:A$100,0)),LOOKUP(2,1/(A$2:A$100=A2),B$2:B$100)>IF(LOOKUP(2,1/(A$2:A$100=A2),B$3:B$101)="",10^6,LOOKUP(2,1/(A$2:A$100=A2),B$3:B$101))),"Stop","Ok")

M.

 

[SteveNL86]

Thanks Marcelo! It does work and gets me the right direction.

One issue I did notice when testing was that it doesn't look back more than a row. For example when I change the values of 1,3 to 12 it doesn't seem to work correctly.

Also when I apply it on the sample data I get a stop on all the 1's.

 

[Marcelo Branco]

Steve,

I was trying to guess what you really want, because, at least for me, the logic is not clear.

About the issue you mentioned: changing the values of 1.3 to 12 i got...

	A​	B​	C​
1​	Value1​	Value2​	Result​
2​	0,7​	10,1​	Stop​
3​	0,7​	10,1​	Stop​
4​	0,8​	9​	Stop​
5​	0,8​	9​	Stop​
6​	1​	10,5​	Ok​
7​	1​	10,5​	Ok​
8​	1​	10,5​	Ok​
9​	1,2​	11​	Ok​
10​	1,2​	11​	Ok​
11​	1,3​	12​	Ok​
12​	1,3​	12​	Ok​
13​	1,5​	12​	Stop​
14​	1,5​	17​	Stop​
15​	1,6​	15​	Stop​
16​	1,6​	15​	Stop​

<tbody>
</tbody>

The logic i've used in my formula is:
If Column B of first_element of a serie in A is less than precedent value in B
OR
Column B of last_element of a serie in A is greater than subsequent value in B Then
"Stop"
Else
"OK"
End If

But, as you said, this is not correct, so i don't knowv how to help you.

Maybe you should create a new thread, showing some examples and explaining clearly the logic to achieve the results. Doing so others members can also try to help you.

M.