VBA loop within a loop, to identify identical values

pvman

New Member
Joined
Apr 4, 2006
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
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:

Order CodeMark
0100120NOT OK
0100120NOT OK
0100120NOT OK
0100120NOT OK
010012DNOT OK
010059ANOT OK
010059CNOT OK
010059CNOT OK
0105760OK
0105760OK
0105760OK
0105760OK
010725DNOT OK
0109460OK
0109460OK
0109460OK
0109460OK
0115510OK
0115670NOT OK
011597ANOT OK
011597BNOT OK
011597XNOT OK
011597ZNOT OK

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
Code:
=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.
 
Upvote 0
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.
 
Upvote 0
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:
Code:
=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 :oops:
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top