I have a spreadsheet with some repeated data. I need to search the list
and only count new occurances and list the count on sheet2. Sheet2 also
contains in col A a log of numbers that have been already counted. I
will list the steps of what I am trying to accomplish below. Any help
that anyone can offer would be appreciated.
__________________________________________________ __________________
Search sheet1 Column C

IF data in cell contains "Complete Task" somewhere in it THEN

Compare value in that row col A with all values in col A on sheet2 to
try to find a match.

IF there is a match THEN

Don't count and continue searching for the next occurance of "Complete
Task"

ELSEIF there is no match then add 1 to counter and add value in Sheet1
col A (same row) to end of col A on sheet2

ELSE
Continue searching the list for next occurance until end of file

ENDIF
__________________________________________________ ___________
Here is the code that I have so far:
-----------------------------------------

Sub Gate1()

Dim c As Range
Dim datarng As Range
Dim Gate1 As String
Dim counter

Gate1 = "Complete ECN Task"
counter = 0

Sheets("Sheet3").Select

Set datarng = Sheets("Sheet3").Range("C1:C" & _
Sheets("Sheet3").Range("C65536").End(xlUp).Row)

For Each c In datarng
If Range("C" & c).Value = "*" & Gate1 & "*" Then
If Range("A:A").Value = Sheets("Sheet4").Range("A:A").Value Then
counter = counter + 0
Else
Sheets("Sheet3").Range("A" & c).Value.Copy
Sheets("Sheet4").Range("A" & c).Paste
counter = counter + 1
End If
Else: End If
Next c

Sheets("Sheet2").Range("E5").Value = counter

End Sub
--------------------------------------------------------------------
Hope this is descriptive enough.

Thank you for the help

Morry


---
Message posted from http://www.ExcelForum.com/

Here's a first stab at it

Sub Gate1()

Dim c As Range
Dim datarng As Range
Dim Gate1 As String
Dim counter

Gate1 = "Complete ECN Task"
counter = 0

With Sheets("Sheet3")
.Select
Set datarng = .Range("C1:C" & .Range("C65536").End(xlUp).Row)
End With

For Each c In datarng
If c.Value Like "*" & Gate1 & "*" Then
If WorksheetFunction.CountIf(Sheets("Sheet4").Rows(c. Row), _
Range("A" & c.Row).Value) 0 Then
Else
Range("A" & c.Row).Copy Destination:= _
Sheets("Sheet4").Range("A" & c.Row)
counter = counter + 1
End If
Else: End If
Next c

Sheets("Sheet2").Range("E5").Value = counter

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"morry " wrote in message
...
I have a spreadsheet with some repeated data. I need to search the list
and only count new occurances and list the count on sheet2. Sheet2 also
contains in col A a log of numbers that have been already counted. I
will list the steps of what I am trying to accomplish below. Any help
that anyone can offer would be appreciated.
__________________________________________________ __________________
Search sheet1 Column C

IF data in cell contains "Complete Task" somewhere in it THEN

Compare value in that row col A with all values in col A on sheet2 to
try to find a match.

IF there is a match THEN

Don't count and continue searching for the next occurance of "Complete
Task"

ELSEIF there is no match then add 1 to counter and add value in Sheet1
col A (same row) to end of col A on sheet2

ELSE
Continue searching the list for next occurance until end of file

ENDIF
__________________________________________________ ___________
Here is the code that I have so far:
-----------------------------------------

Sub Gate1()

Dim c As Range
Dim datarng As Range
Dim Gate1 As String
Dim counter

Gate1 = "Complete ECN Task"
counter = 0

Sheets("Sheet3").Select

Set datarng = Sheets("Sheet3").Range("C1:C" & _
Sheets("Sheet3").Range("C65536").End(xlUp).Row)

For Each c In datarng
If Range("C" & c).Value = "*" & Gate1 & "*" Then
If Range("A:A").Value = Sheets("Sheet4").Range("A:A").Value Then
counter = counter + 0
Else
Sheets("Sheet3").Range("A" & c).Value.Copy
Sheets("Sheet4").Range("A" & c).Paste
counter = counter + 1
End If
Else: End If
Next c

Sheets("Sheet2").Range("E5").Value = counter

End Sub
--------------------------------------------------------------------
Hope this is descriptive enough.

Thank you for the help

Morry


---
Message posted from http://www.ExcelForum.com/