Working with huge data is always exciting and challenging. From the 2007 version onward, Excel is supporting more than a million rows in each worksheet.
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
VBA code that will sum cells by its color through excel function. This code will really help in making the analysis and presentation better.
TRIM function is used to remove the additional spaces (i.e. spaces before/after/between the words) except for single space between words.
Random Rows Selector is an MS Excel based tool which can be used to pick random or stratified samples from a set of records available in Excel.
Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…
This article has Top Excel Interview Questions and Answers to test your Excel skills. It’s great for beginners who are just starting out, or even pros who want a refresher. The questions cover everything you might need for jobs like data analyst, business analyst, or accountant.
Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…