Duplicate Data

mwhit

Board Regular
Joined
Feb 20, 2006
Messages
163
Hello,

What's the best way to delete duplicate data?? For example, Column A has the following:

Car
Cars
Cars
bat
bat
ball

I will like to delete the duplicate of Cars and bat. Is this possible in Excel? Thanks in advance.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
You can use advanced filter on the Data menu.

Select Copy to another location...in List Range select your list of duplicated data...leave Criteria Range blank and in Copy to select a cell in a blank column on your sheet...finally select the Unique records only option.

This will then give you a de-duplicated list.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
Take a look at the "Unique Records Only" option under Advanced Filters.
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
do the above meet you requirements, are you wanting to extract a unique list to work with or "delete" the duplicates, using advanced filter wont actually delete them just filter them out, you could use this method to get a unique list then delete the origonal list or follow the link for more options on duplicates, read the part whicha says delete rows

http://www.cpearson.com/excel/deleting.htm
 

mwhit

Board Regular
Joined
Feb 20, 2006
Messages
163

ADVERTISEMENT

All- thanks for the replys. I did figure it out. :biggrin: However, i do have a new challange: Is it possible to filter on one column when you have data in six columns?? :confused: :confused:

For example; I have data in columns A-F. Column B has the data that i will like to filter out duplicate info but keep all the other columns in tact. Is that possible or is there another way to get what i need. Thanks in advance!

Michael
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Sure. When you select Advanced Filter it has a tendency to select your whole block of data as the list to be filtered.

In the post I did just make sure that the List Range only covers the column(s) that you want to filter and not the whole range of data.

Dom
 

mwhit

Board Regular
Joined
Feb 20, 2006
Messages
163

ADVERTISEMENT

Domski,

Hey, the selected range is fine but i only want to filter on column B. In essence, i want the rest of the data that goes with the selected range. Does that make sense?

Maybe i need to copy column B to column A... Just a thought :rolleyes:
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
How about using Autofilter? You can then filter on one or more columns quite easily.

Other than that can you post a sample of your data and the result you're trying to achieve.

Dom
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Code:
Sub RemoveDupes() 
Dim Rng As Range 
Dim i   As Long 

i = Range("A" & Rows.Count).End(xlUp).Row 
Set Rng = Range("B1:B" & i) 
With Rng 
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True 
    .SpecialCells(xlCellTypeVisible).Select 
    ActiveSheet.ShowAllData 
    Selection.EntireRow.Hidden = True 
    .SpecialCells(xlCellTypeVisible).ClearContents 
    .EntireRow.Hidden = False 
End With 
End Sub
 

Forum statistics

Threads
1,141,755
Messages
5,708,320
Members
421,565
Latest member
Lastadiego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top