how to delete rows based on duplicate information in a column

A

albertpinto

Is there a way (beside writing a macro) to delete if you have identical
entries in a column and you want to keep only one copy of that row. For
e.g.

A B C
1 2 3
1 4 5

Since there are Two 1's in A i only want to keep one copy of that row.

Thanks in advace for help,
 
P

Paul Lautman

albertpinto said:
Is there a way (beside writing a macro) to delete if you have
identical entries in a column and you want to keep only one copy of
that row. For e.g.

A B C
1 2 3
1 4 5

Since there are Two 1's in A i only want to keep one copy of that row.

Thanks in advace for help,
Click to expand...

Data->Advanced filter has an option for removing duplicates
 
D

Dave Peterson

With headers in Row 1, you can use Data|Filter|Advanced filter to show just the
unique entries.

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Do this based on just column A.

Then you can select your range (all columns)
edit|copy
and edit|paste to a new location (a new sheet?)

Only the visible cells should be copied|Pasted.
 
J

JB

We assume number sorted in column A:

Range("A1").Select
Do While ActiveCell <> ""
If ActiveCell = ActiveCell.Offset(-1, 0) Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Cordialy JB
 
A

aresen

The unique record implementation will yield the first occurrence of
each value in the list. That's fine if it's the first you want.
However, in some cases the last occurrence is more valid since data is
usually added to the end of a list and is the most recent.
Let's say the last row of data is 100. To extract the last occurrence,
select cells D2 and enter the formula =MATCH(A2,A3:A$101,0). Copy that
down through to cell D100. You will get numbers and #N/A. Now select
column D and use Edit... Go To... Special... Select Formulas but
deselect Errors then hit OK. Now Edit... Delete... Entire Row. Now you
can delete the formulas left in column D. Conversely, if the formula is
=MATCH(A2,A$1:A1,0) and then copied down, you'll be retaining the first
instances.
 
Top