Show which entries in Search Table duplicates or triplicets
I flicense plate number are in Sheet5 column A, then in Sheet6 column A will
be the duplicate plate numbers and in column B will be a count of the number
of times the license plate was found. Code assumes no header rows and the
plates in sheet 5 are sorted.
Sub getduplicates()
NewRowCount = 1
OldRowCount = 1
StartRow = 1
With Sheets("Sheet5")
Do While .Range("A" & OldRowCount) < ""
If .Range("A" & OldRowCount) < _
.Range("A" & (OldRowCount + 1)) Then
If OldRowCount - StartRow = 1 Then
'Duplicatge copy to new sheet
License = .Range("A" & OldRowCount)
Occurances = OldRowCount - StartRow + 1
With Sheets("Sheet6")
.Range("A" & NewRowCount) = License
.Range("B" & NewRowCount) = Occurances
NewRowCount = NewRowCount + 1
End With
End If
StartRow = OldRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub
"DLH" wrote:
We have a parking problem. We write down license numbers of cars in the lot
at various times on various days. There are hundreds of entries. Now we
sort by license and see which cars have been here before, or which have NEVER
moved. I need a report of JUST the duplicate, triplicate or multiple
occurrences. IOW, if the entry is there more than once, I need to extract
that entry as a DOUBLE or a TRIPLE or worse. THen we target those worse
offenders
How can I do this with a defined range of date-time-license no-lot (we have
several lots). Thanks.
|