VBA loop within a loop, to identify identical values

[pvman]

Hello experts,

I am sorry about the title. It might not be the best title to describe what I am looking for...

I am actually trying to create a code that will loop through orders (order lines) and check the codes next to each order number.
If all the lines of code next to each order are ZERO - the "mark" of ALL the lines of the order will be OK.
There might be several identical order lines (order number wise) and the codes next to each order number might be the same.

If even ONE of the codes next to each order line is NOT zero - the "mark" for ALL the SAME orders will be "NOT OK".

The database is sorted by order number and then by code (in real life there are a few thousands of lines)

Any idea what will be the best approach to achieve the "mark"?
TIA

Sample database:

[TABLE="width: 208"]
<tbody>[TR]
[TD="align: left"]Order [/TD]
[TD][/TD]
[TD]Code[/TD]
[TD][/TD]
[TD="align: left"]Mark[/TD]
[/TR]
[TR]
[TD="align: left"]010012[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010012[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010012[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010012[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010012[/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010059[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010059[/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010059[/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010576[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010576[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010576[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010576[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010725[/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]010946[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010946[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010946[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]010946[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]011551[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]OK[/TD]
[/TR]
[TR]
[TD="align: left"]011567[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]011597[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]011597[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]011597[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
[TR]
[TD="align: left"]011597[/TD]
[TD][/TD]
[TD]Z[/TD]
[TD][/TD]
[TD="align: left"]NOT OK[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 142"]
<tbody>[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Assuming that your data is in columns A and B, the header is in row 1, so the data starts in row 2, and "Mark" is in column C, put this in column C and copy down:

=IF(COUNTIFS(A:A,A2,B:B,"<>0")>0,"NOT OK","OK")

By the way, I think 011567 show be "OK", according to your rules. It only has one record and it is zero.

 

[Jeffrey Mahoney]

I created a formula starting in cell C2
=IF(COUNTIFS($A$2:$A$24,A2,$B$2:$B$24,"<>"&0)>0,"NOT OK","OK")

Change the range so it encompasses your entire set of date: $A$2:$A$24 should be $A2:$A100000. Copy it down to the last cell of your data set.

 

[Jeffrey Mahoney]

Ok, you beat me Joe!

 

[pvman]

Assuming that your data is in columns A and B, the header is in row 1, so the data starts in row 2, and "Mark" is in column C, put this in column C and copy down:

=IF(COUNTIFS(A:A,A2,B:B,"<>0")>0,"NOT OK","OK")

By the way, I think 011567 show be "OK", according to your rules. It only has one record and it is zero.

That was a really fast reply
I will give it a try

As for your question re 11567, you are correct. I made a typing mistake

 

[pvman]

I created a formula starting in cell C2
=IF(COUNTIFS($A$2:$A$24,A2,$B$2:$B$24,"<>"&0)>0,"NOT OK","OK")

Change the range so it encompasses your entire set of date: $A$2:$A$24 should be $A2:$A100000. Copy it down to the last cell of your data set.

Thank you very much, as well