One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates.
In this article, we will be sharing 4 ways to delete duplicate records from your data.
In the first method, let’s make use of Excel’s inbuilt functionality of removing duplicates.
Note: If your data has extra spaces at the end of the value (for example ‘Friedrick’ and ‘Friedrick’ then Excel may not be able to detect those duplicates. In this method, we will use VBA code to call the Remove Duplicates function and delete the duplicate records
Following is the VBA Syntax and sample VBA macro command to delete duplicate rows from the worksheet using VBA. We are using the Remove Duplicates method of the Cells object of the worksheet.
Cells.RemoveDuplicates Columns:=Array(
[Column Numbers])
Here Cells.Remove Duplicates command tells excel to remove the duplicated based on the specified list of column array. Columns:=Array([Column Numbers]) will help us to specify the list of columns to combine and treat it as a duplicate record.
Sub sbRemoveDuplicates() Cells.RemoveDuplicates Columns:=Array(1) End Sub
'VBA code to remove duplicates from data with headers Sub sbRemoveDuplicatesSpecificWithHeaders() Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlYes End Sub
‘Starting procedure to write VBA code to remove duplicates from data with no headers Sub sbRemoveDuplicatesSpecificWithNoHeaders() Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlNo End Sub
File Manager tool is an Excel based tool which helps you to delete or move unwanted files from your system. It requires a source and destination folder (in case you want to move files). First it lists all the files available in the folder or sub-folders then you can select the action to be taken for each file such as Move or Delete. With a click of button, tool will take all necessary actions.
Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.
How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…
Index Match in Excel are powerful tools for advanced lookups. They’re popular because they’re incredibly flexible. You can use them to search horizontally or vertically, perform two-way searches, find values to the left of your search criteria, handle case-sensitive searches, and even search using multiple conditions. If you want to improve your Excel skills, mastering INDEX and MATCH is essential. Below, you’ll find numerous examples to help you grasp how they function.
Excel has built-in options to make graphs, it doesn’t have a heat map feature. However, you can quickly and easily create a heat map in Excel using conditional formatting.
Time & Motion Tracker is an MS Excel based tool which helps you to track Start and End time of any type of transaction or activity. The tool is developed using VBA coding which helps you to protect manual manipulation in the data by the user. It is also easy to use, just click on Start (shortcut: Ctrl+Shift+A) or Stop (Ctrl+Shft+S) buttons to record the time stamp.