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.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Value1​

[/td][td]

Value2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0,7​

[/td][td]

10,1​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

0,7​

[/td][td]

10,1​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

0,8​

[/td][td]

9​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

0,8​

[/td][td]

9​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1​

[/td][td]

10,5​

[/td][td]

Ok​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1​

[/td][td]

10,5​

[/td][td]

Ok​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1​

[/td][td]

10,5​

[/td][td]

Ok​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1,2​

[/td][td]

11​

[/td][td]

Ok​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

1,2​

[/td][td]

11​

[/td][td]

Ok​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

1,3​

[/td][td]

13​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

1,3​

[/td][td]

13​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

1,5​

[/td][td]

12​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

1,5​

[/td][td]

17​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

1,6​

[/td][td]

15​

[/td][td]

Stop​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

1,6​

[/td][td]

15​

[/td][td]

Stop​

[/td][/tr]
[/table]

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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Value1​

[/TD]
[TD]

Value2​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

0,7​

[/TD]
[TD]

10,1​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

0,7​

[/TD]
[TD]

10,1​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

0,8​

[/TD]
[TD]

9​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

0,8​

[/TD]
[TD]

9​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

10,5​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

1​

[/TD]
[TD]

10,5​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

1​

[/TD]
[TD]

10,5​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

1,2​

[/TD]
[TD]

11​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

1,2​

[/TD]
[TD]

11​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

1,3​

[/TD]
[TD]

12​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

1,3​

[/TD]
[TD]

12​

[/TD]
[TD]

Ok​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

1,5​

[/TD]
[TD]

12​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

1,5​

[/TD]
[TD]

17​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

1,6​

[/TD]
[TD]

15​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

1,6​

[/TD]
[TD]

15​

[/TD]
[TD]

Stop​

[/TD]
[/TR]
</tbody>[/TABLE]


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.