matching random text

  • Thread starter Thread starter Lance\\Sheri
  • Start date Start date
L

Lance\\Sheri

I've done a couple simple macros, but new at this and can't figure this one
out. It would search column a for a stock symbol listed there 3 or more
times and copy and paste that stock to column b. An example of what I would
like the macro to do is below. If this is possible, any help would be
appreciated, especially the code that would select the stock if it appears
in column a 3 or more times. Thanks in advance. Lance



Column A Column B
ALKS DECK
ASVI
BEBE
BLX
BLX
BONT
CAE
CETV
CNTE
CRFT
CRMT
CRMT
DECK
DECK
DECK
 
Lance,
Try this macro
Row1 must have a heading for advanced filter to work
Cecil

Sub Macro1()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LRA = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LRA).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), Unique:=True

LRB = Range("B" & Rows.Count).End(xlUp).Row

For i = LRB To 2 Step -1
If Evaluate("COUNTIF(" & Range("A2:A" & LRA).Address _
& "," & Range("B" & i).Address & ")") < 3 Then
Range("B" & i).Delete Shift:=xlUp
End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Back
Top