Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show which entries in Search Table duplicates or triplicets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list of entries from a column without duplicates | Excel Worksheet Functions | |||
pivot table duplicates numbers under 'drop down show all'? | Excel Discussion (Misc queries) | |||
My pivot table field list show duplicates | Excel Discussion (Misc queries) | |||
Summing duplicate entries and non duplicates | Excel Programming | |||
Copy multiple entries and paste without duplicates | Excel Programming |