VBA loop within a loop, to identify identical values

pvman

New Member
Joined
Apr 4, 2006
Messages
34
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>
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,727
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.
 

pvman

New Member
Joined
Apr 4, 2006
Messages
34
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 :banghead:
 

pvman

New Member
Joined
Apr 4, 2006
Messages
34
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,362
Messages
5,468,174
Members
406,569
Latest member
Quest_

This Week's Hot Topics

Top