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]

0.7	10.1	Stop
0.7	10.1	Stop
0.8	9	Stop
0.8	9	Stop
1	10.5	Ok
1	10.5	Ok
1	10.5	Ok
1.2	11	Ok
1.2	11	Ok
1.3	13	Ok
1.3	13	Ok
1.5	12	Stop
1.5	17	Stop
1.6	15	OK
1.6	15	Ok

<tbody>
</tbody>

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.