Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Delete Unique Values From A Column In Excel ?
Excel is a powerful tool widely used for data management and analysis. When working with large datasets, it's common to come across scenarios where you need to remove unique values from a column. Whether you want to filter out one−time occurrences, identify and eliminate outliers, or streamline your data for specific purposes, deleting unique values can be a handy technique. In this tutorial, we will walk you through the process of deleting unique values from a column in Excel. We will cover different methods that can be used, depending on your requirements and the version of Excel you are using. You'll learn how to identify unique values, select and delete them, and explore some advanced techniques for handling unique data.
By the end of this tutorial, you will have a clear understanding of how to effectively delete unique values from a column in Excel, empowering you to manipulate your data more efficiently and achieve your desired outcomes. So let's get started and dive into the various methods you can employ to remove unique values from an Excel column!
Delete Unique Values From A Column
So let us see a simple process to know how you can delete unique values from a column in Excel.
Step 1
Consider an excel sheet which contain list of names with duplicate and unique values as similar to below image.
First right click on sheet name and select view code to open VBA application then click on insert and select module and copy the below code in to textbox as similar to below image.
Right click > View code > Insert > Module > Copy.
Code
Sub DeleteUnique()
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim Dic As Variant
On Error Resume Next
xTitleId = "Range of cells"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.Columns(1)
Set Dic = CreateObject("Scripting.Dictionary")
Arr = WorkRng.Value
For i = 1 To UBound(Arr, 1)
xValue = Arr(i, 1)
Dic(xValue) = Dic(xValue) + 1
Next
WorkRng.ClearContents
Arr = WorkRng.Value
xIndex = 1
For Each xKey In Dic.keys
xValue = Dic(xKey)
If xValue > 1 Then
For i = 1 To xValue
Arr(xIndex, 1) = xKey
xIndex = xIndex + 1
Next
End If
Next
WorkRng.Value = Arr
End Sub
Step 2
Then save the sheet as macro enabled template and click F5 to run the module and select the range of cells and click ok to complete the task.
Save > F5 > Select cell > Ok.
Conclusion
In this tutorial we have used a simple example to demonstrate how you can delete unique values from a column in Excel to highlight particular set of data.
485 Views
