Writing Complex Macro or VBA Code?

[Guest]

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.

 

[Mike Fogleman]

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

 

[Guest]

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

 

[Guest]

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]

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

 

[Guest]

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.