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,


--
albertpinto
------------------------------------------------------------------------
albertpinto's Profile: http://www.excelforum.com/member.php...fo&userid=9370
View this thread: http://www.excelforum.com/showthread...hreadid=546032

albertpinto wrote:
> 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,

Data->Advanced filter has an option for removing duplicates

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.

albertpinto wrote:
>
> 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,
>
> --
> albertpinto
> ------------------------------------------------------------------------
> albertpinto's Profile: http://www.excelforum.com/member.php...fo&userid=9370
> View this thread: http://www.excelforum.com/showthread...hreadid=546032

--

Dave Peterson

Try this freeware:
http://www.aks-labs.com/support/phpB...topic.php?t=69
This is not Excel plug-in, but works for duplicates finding

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

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.