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.

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.

Assume Critical Spares is in column A and System is in column B. In the
Marked column put (say C2)
In the next available column, put in a formula like
=if(And(A2=1,countif($B:$B,B1)1),"Marked","")

then drag fill down the column.

You can then filter on this column if necessary.

if you wanted a macro

sub AddMarks()
set rng = Range(cells(2,1),Cells(2,1)).End(xldown)
With rng.offset(0,2)
.Formula = "=if(And(A2=1,countif($B:$B,B1)1),""Marked"","""" )"
' optional to remove formula and replace with results
' .Formula = .Value
End With

End sub
--
Regards,
Tom Ogilvy


"Sohail iqbal" wrote:

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.

That was great help and brisk also, I must thankyou both guys Mike and Tom.
I'm sorry I forgot to tell you some important piece of information. Actaul
scenario is similar to what i posted earlier but what I missed I'll post it
altogether so as to make sure that it makes some sense.
1) I've to check values in column A(PartNumber) if it is unique then I'll
check column B(Critical Spares) for value 1, if it is 1 then i'll mark 1 in
column C(Marker).
2) While checking for Column B if I get more than one row of data for that
part number then I need loop through these rows to see if any among these
contains 1 for Column B if so I'll mark 1 for all of these columns.

I hope this will be clear, I again request you to reply ASAP.
thanks and regards.

"Mike Fogleman" wrote:

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.

Your first post was clearer, now it makes less sense with the addition of
the Part Number column. Does the System column no longer play a part in this
scenario, and instead use the Part Number column? Or do all 4 columns play a
part. Both responses from Tom and I required no pre-filtering of the
Critical Spares column. Perhaps this is where the communication break-down
is. Your new post is contradictory in regards to column A, Part Number. In
step 1) you check to see if the Part Number is unique and column B is a 1.
In step 2) if column B is a 1 then look for duplicates of that part number,
which we just determined to be unique in step 1)?? What this would end up
doing is putting a 1 in column C for every 1 in column B because it doesn't
matter whether column A is unique or not. I am almost sure this is not what
you want.

Mike F
"Sohail iqbal" wrote in message
...
That was great help and brisk also, I must thankyou both guys Mike and
Tom.
I'm sorry I forgot to tell you some important piece of information. Actaul
scenario is similar to what i posted earlier but what I missed I'll post
it
altogether so as to make sure that it makes some sense.
1) I've to check values in column A(PartNumber) if it is unique then I'll
check column B(Critical Spares) for value 1, if it is 1 then i'll mark 1
in
column C(Marker).
2) While checking for Column B if I get more than one row of data for that
part number then I need loop through these rows to see if any among these
contains 1 for Column B if so I'll mark 1 for all of these columns.

I hope this will be clear, I again request you to reply ASAP.
thanks and regards.

"Mike Fogleman" wrote:

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.

Thanks again and sorry for this blunder, yes System name and branch are the
two names of same column. Rest is same I hope now it'll be clear.

"Mike Fogleman" wrote:

Your first post was clearer, now it makes less sense with the addition of
the Part Number column. Does the System column no longer play a part in this
scenario, and instead use the Part Number column? Or do all 4 columns play a
part. Both responses from Tom and I required no pre-filtering of the
Critical Spares column. Perhaps this is where the communication break-down
is. Your new post is contradictory in regards to column A, Part Number. In
step 1) you check to see if the Part Number is unique and column B is a 1.
In step 2) if column B is a 1 then look for duplicates of that part number,
which we just determined to be unique in step 1)?? What this would end up
doing is putting a 1 in column C for every 1 in column B because it doesn't
matter whether column A is unique or not. I am almost sure this is not what
you want.

Mike F
"Sohail iqbal" wrote in message
...
That was great help and brisk also, I must thankyou both guys Mike and
Tom.
I'm sorry I forgot to tell you some important piece of information. Actaul
scenario is similar to what i posted earlier but what I missed I'll post
it
altogether so as to make sure that it makes some sense.
1) I've to check values in column A(PartNumber) if it is unique then I'll
check column B(Critical Spares) for value 1, if it is 1 then i'll mark 1
in
column C(Marker).
2) While checking for Column B if I get more than one row of data for that
part number then I need loop through these rows to see if any among these
contains 1 for Column B if so I'll mark 1 for all of these columns.

I hope this will be clear, I again request you to reply ASAP.
thanks and regards.

"Mike Fogleman" wrote:

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.