Duplicate Data

[mwhit]

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.

 

[Domski]

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]

Take a look at the "Unique Records Only" option under Advanced Filters.

 

[shippey121]

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]

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

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]

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]

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

 

[Domski]

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]

Hi,

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