How to delete repeted rows automaticaly?

[gabarrao]

Hello,

I have a very big list of adresses and phones. There are a lot o
repeted data in it.
I would like to know if there is a way to erase the repeted rows of m
list automaticaly. If it is possible, how can I do it?

Thanks very much.

Matheu

 

[Guest]

I would sort the rows (Data, Sort) and then delete the duplicated rows which
will be obvious. Take a note of the original row sequence before the first
sort so you can reresort it back to the original sequence after the deletion
(if required).

 

[gabarrao]

Hello,

I have already done this. But even this way the list is too big.
would like to find and automatic way.

Thanks very much,

Matheu

 

[IC]

Hello,

I have already done this. But even this way the list is too big. I
would like to find and automatic way.

Thanks very much,

Matheus

This macro assumes your data starts at row 1, that column A is the one you
want to test for duplicates and that the data has been sorted on column A.
Change the Row= and Col= lines to change the target cells.

It will keep the first row of any dupes and continue until it finds a 2
blank cells.

Sub deletedupes()
Row = 1
Col = 1
Do Until Cells(Row, Col).Value = ""
Cells(Row + 1, Col).Activate
If Cells(Row + 1, Col) = Cells(Row, Col) Then
ActiveCell.EntireRow.Delete
Else
Row = Row + 1
End If
Loop
End Sub

Ian

 

[Gord Dibben]

Data>Filter>Advanced Filter

Check "unique records only" and "copy to another location".

For more on this method see Debra Dalgleish's site

http://www.contextures.on.ca/xladvfilter01.html

Gord Dibben Excel MVP

 

[Mark]

Hi,
I wrote something recently for another user and the main
problem was that although I created each string in upper
case it does not take into account that one cell had ST
and the other cell had Street. You have to make sure that
rrepeated lines are actually repeats, word for word. Send
me your email and I will send it to you.

(e-mail address removed)

- -mark

see my excel stuff:
http://au.geocities.com/excelmarksway
http://www.geocities.com/excelmarksway