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

 

[SteveNL86]

Additionally, in 2 other columns I'm trying the same thing but in these 2 I'm allowing the results to vary within a limit. That limit is either +1 or -1 but it's not allowed to be both +1 and -1.

 

[SteveNL86]

Let me give some example data to illustrate that second issue:

A B C
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 Ok
1 12 Ok
1 13 Ok
1,1 14 Ok
1,1 14 Ok
1,1 14 Ok

This data would be cleared ok.

A B C
0,7 5 Ok
0,7 5 Ok
0,7 5 Ok
0,8 10 Ok
0,8 10 Ok
1 11 STOP
1 12 STOP
1 13 STOP
1,1 14 Ok
1,1 14 Ok
1,1 14 Ok

This data wouldn't.

Is this possible with a formula?

 

[SteveNL86]

I've gotten as far as the following formula: =IF(AND(COUNTIF($AR$2:$AR$100;"=>AR2");COUNTIF($AS$2:$AS$100;"=>AS2"));"STOP";"Ok")
Yet it doesn't seems to work when I alter the value in AS2 to a higher value.

 

[Marcelo Branco]

Assuming the data are A2:B100 try
C2 copied down
=IF(COUNTIFS(A$2:A$100,A2,B$2:B$100,B2)=COUNTIF(A$2:A$100,A2),"OK","STOP")

Hope this helps

M.

 

[SteveNL86]

Thanks for helping Marcelo!

It seems to work great.

I'm running into another issue in the column next to it though. It's a control column that should say stop if the data from the previous row is higher than the next row.
I've tried changing the formula you provided to this:
=IF(COUNTIFS(AR$2:AR$100,AR2,AS$2:AS$100,AS2)>COUNTIF(AR$2:AR$100,AR2),"OK","STOP")

But that doesn't seem to work.

 

[Marcelo Branco]

You are welcome.
Try to post a data sample along with expected results.

M.

 

[SteveNL86]

[TABLE="width: 500"]
<tbody>[TR]
[TD]0.7[/TD]
[TD]10.1[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]0.7[/TD]
[TD]10.1[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]0.8[/TD]
[TD]9[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]0.8[/TD]
[TD]9[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10.5[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10.5[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10.5[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]11[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]11[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]13[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]13[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]12[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]17[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]1.6[/TD]
[TD]15[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]1.6[/TD]
[TD]15[/TD]
[TD]Ok[/TD]
[/TR]
</tbody>[/TABLE]

So a stop on 0.7 because the value is higher than the 0.8. A stop on 0.8 because it's less than the 0.7 and a stop on the first 1.5 because it's lower than 1.3 and a stop on second 1.5 because it higher than 1.6.

 

[Marcelo Branco]

I'm confused
Don't understand why 1,6 = OK. As 15 is less than 17 shouldn't be Stop?

M.

 

[SteveNL86]

Yes, my bad. That one should be stop aswell. Sorry I don't see an edit button, but you are correct.