Help needed!!!!

[GohLS]

Hi i am currently working on a excel formular,
and i am stuck, need some help from you guys.

I need to check if the "Order Number" is complete or Not Complete,
however checking by manual is very hard.

So i would like to check if you guys can help on this.
I have attached the excel for reference.
anyone can help by using excel formular or marco is appreciate.

I did some explaination in the excel attached.
Kindly help

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextItem As Long
Dim BreakIndex As Long
Dim aryItems As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
ReDim aryItems(1 To LastRow)
For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Not Complete" Then

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then

NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i

BreakIndex = NextItem

For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Complete" Then

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then

NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i

For i = 1 To NextItem

.Cells(i + 3, "F").Value = aryItems(i)
.Cells(i + 3, "G").Value = IIf(i > BreakIndex, "", "Not ") & "Complete"
Next i
End With

End Sub
[/vba]

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextItem As Long
Dim BreakIndex As Long
Dim aryItems As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
ReDim aryItems(1 To LastRow)
For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Not Complete" Then

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then

NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i

BreakIndex = NextItem

For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Complete" Then

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then

NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i

For i = 1 To NextItem

.Cells(i + 3, "F").Value = aryItems(i)
.Cells(i + 3, "G").Value = IIf(i > BreakIndex, "", "Not ") & "Complete"
Next i
End With

End Sub
[/vba]