Finding Duplicates In An Excel Spreadsheet With VBA

If you have ever been confronted with an Excel spreadsheet that has reproduction entries, you will understand that a few care is required. You do not want to delete what is probably genuine unique entries, or miss out on different entries that might be duplicates but have slipped through cd duplication.

Some duplicates might maintain facts that you want to retain or transfer to a new entry
You would possibly need to feature statistics to new or current entries, as an instance to feature a unique key
Some entries can be partial duplicates requiring further research
Excel Tools for Finding Duplicates

Excel has some excellent tools for identifying and putting off same entries but these can be a bit rigid in case you’re trying to do something barely specific together with your records:

Conditional formatting allows highlighting of viable duplicates and is a short way to find repeated entries
The remove duplicates device is a blunt tool which deletes the offending entries and reviews at the number of gadgets removed
Various worksheet formulas can become aware of duplicate entries
But once in a while the standard gear might not quite do the job and it’s correct to know what to do if you want some thing only a little extra complex. With a touch know-how and planning you can write your very own VBA code to find duplicate entries.

Finding Duplicates With VBA

We’re going to write down some VBA code with the intention to pick out replica entries and replica them to a new worksheet. This is probably a common challenge in business as you would possibly want to review the feasible duplicates in preference to just delete them.

A excellent example is probably a patron listing which you suspect may have repeated entries. We need our code to search the cellphone numbers looking for replica facts; if a cellphone wide variety is repeated there’s an excellent threat the entries are duplicates.

Select the sheet and cells to look

Sheets(“allData”).Activate
dupsCol = “e1”
Range(dupsCol).Activate
lastCell = ActiveCell.End(xlDown).Address
allCells = ActiveCell.Address & “:” & lastCell
Range(allCells).Select
Now we’ll loop through the statistics and compare each mobile to the entire information set. If we discover a reproduction we’ll reproduction the row to the “duplicates” worksheet.

Ct = zero
For Each c In Selection
curCell = c.Value
If Application.WorksheetFunction.CountIf(Range(allCells), curCell) > 1 Then
ct = ct + 1
Sheets(“alldata”).Range(“a” & c.Row).EntireRow.Copy Sheets(“duplicates”).Range(“a” & ct)
End If
Next
Now we will type the duplicates to cause them to easier to paintings with.

Sheets(“duplicates”).Activate
Range(“A1”).CurrentRegion.Select
Selection.Sort Key1:=Range(dupsCol)
Summary
With a bit making plans and information it is able to be easy to improve the same old equipment that include each version of MS Excel. Learning a touch about VBA and how it is able to enhance your spreadsheet processes will pay dividends each in saving time and improving productiveness.

Andy L Gibson is a former Web Site programmer rediscovering his hobby in software program programs for small enterprise. Previously he has used AJAX, XML and ASPX to application Web Sites for eating places, public sale houses and Heath Authorities. He is eager to reply questions and explore commercial enterprise opportunities anywhere they could gift themselves.