Wednesday, May 7, 2008

Find duplicate entries in excel

Go to excel -->tools -->macro - visual basic editor then paste the below code and run..

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

2 comments:

Alex said...

For me excel files are very important, because of I work with theirs on my job. One day I ran into like problem. Luckily for me one program relieved me for a minute and convinced of its abilities for solving out all troubles in this sphere - recover Excel file tools.

Memory-Rosary said...

how about excel files somehow oversize ?
*from 91kb to 21mb ?

thanks