How about using an Advanced Filter? It can hide all the rows of data except
those with duplicate file numbers.
To use an advanced filter, you must have 3 or more blank rows above your data.
To illustrate, assume your column headings are in row 4 and your data is in rows
5 through 150. File number is in column A.
Put the filter criteria in cells A1:A2 (doesn't have to be in column A).
Normally A1 would repeat the headings of the column you want to filter, but
because we are going to use a formula in our criteria, leave A1 blank. In A2,
enter =COUNTIF($A$5:$A$150,A5)>1 . It should return FALSE. It's important
that the first argument is an absolute range ($A$5:$A$150) and the second is a
relative reference (A5).
To run the advanced filter, click on any cell in your data. Select Filter >>
Advanced Filter... from the Data menu. The filter dialog should figure out the
correct list range; you need to tell it the criteria range ($A$1:$A$2). Don't
select unique records only. Click OK.
All rows will be hidden EXCEPT those with duplicate file numbers (If there are
no duplicate file numbers, ALL the rows will be hidden.) After you investigate
the duplicates, you can unhide the other rows by selecting Filter >> Show All
from the Data menu.
All this can be automated with a macro, as follows:
Sub FindDuplicates()
Dim Rng As Range, msg1 As String
On Error GoTo FDerr1
Range("A5").Select
Selection.CurrentRegion.Select
Set Rng = Selection
ActiveSheet.Range("A2").Formula = "=COUNTIF(" & Rng.Address & ",A5)>1"
Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A2"), Unique:=False
Cleanup1:
Set Rng = Nothing
Exit Sub
FDerr1:
If Err.Number <> 0 Then
msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg1$, , "FindDuplicates error", Err.HelpFile, Err.HelpContext
End If
GoTo Cleanup1
End Sub
You could have another button to unhide all the rows afterwards.
Sub ShowAllRows()
'Unhides rows hidden by advanced filter.
ActiveSheet.ShowAllData
End Sub
Hope this is helpful to you.