counter script

[Robert]

Hello,

I need a counter script that can turn this input. I need the number of
inspections to be the number of unique dates. Thanks. (e-mail address removed)

Name Inspection Date
ABC Trucking 1/1/2006
ABC Trucking 1/1/2006
ABC Trucking 1/23/2006
ABC Trucking 1/23/2006

into this output

Name Most recent inspection # of inspections
ABC Trucking 1/23/2006 2

 

[matt]

Hello,

I need a counter script that can turn this input. I need the number of
inspections to be the number of unique dates. Thanks. (e-mail address removed)

Name Inspection Date
ABC Trucking 1/1/2006
ABC Trucking 1/1/2006
ABC Trucking 1/23/2006
ABC Trucking 1/23/2006

into this output

Name Most recent inspection # of inspections
ABC Trucking 1/23/2006 2

Why not sort the data according to date in ascending order? You can
then delete all rows below the most recent date.

For example, after sorting the data you could write something like
this: (This hasn't been tested).

sub xxx()
Dim a
Dim counter
Dim currentDate
Dim nextDate
Dim strAdd
Dim endAdd

counter = Range("a1").currentregion.rows.count

for a = 1 to counter
currentDate = Range("c" & a).value
nextDate = range("c"&a).offset(1,0).value

if currentDate <> nextDate Then
exit for
end if
next

activecell.offset(1,0).select
strAdd = selection.address
selection.end(xldown).select
endAdd = selection.address

range(stradd & ":" & endadd).entirerow.delete

counter = Range("a1").currentregion.rows.count
range("e1").value = "count is " & counter

end sub

matt