Assuming headers in row 1 and columns A:C are System, Critical Spares, and
Marked, respectfully:

Sub TEST()
Dim LRow As Long
Dim c As Range
Dim sysrng As Range, critrng As Range
Dim Sys As Double
Dim SysCrit As Double

LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set sysrng = Range("A2:A" & LRow)
Set critrng = Range("B2:B" & LRow)

For Each c In sysrng
Sys = WorksheetFunction.CountIf(sysrng, c.Value)
SysCrit = WorksheetFunction.SumIf(sysrng, c.Value, critrng)
If Sys 1 And SysCrit 0 Then c.Offset(0, 2).Value = 1
Next c

End Sub


Mike F
"Sohail iqbal" <Sohail wrote in message
...
Hi!

I'm programmer but not very much familiar with VBA. I've situation where
I've 16000 thousands rows of data in my excel sheet. It has got many
columns
but at moment i'm conerned about three of them namely system, critical
spares
and marked. What I want to do is to automate the following process either
by
macro or VBA code.

1) Filter all data where critical spares' value is 1. its pretty simple

2) Since there are duplicate values for system column so i want to look
for
those system which repeat more than once. If I find the let say four
system
each one is repeating three times. I want to be able to add 1 to the
Marked
column for each row with repeated system and for all systems those are
repeated more than once.

I hope I made my point clear, its kinda urgent please repond ASAP.

regards.