Delete duplicate rows from a list in Excel

Hi,

Below are instructions on how to "delete duplicate rows from a list in
Excel". I learned about this tip from the Microsoft Office Assistant
website. However, each time I try step 4, my list is not filtered and
no records are hidden. Is there a secret I am missing to make this
work?

Thanks for your help!

-Greg

http://office.microsoft.com/en-us/assistance/HA010346261033.aspx
Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all
values in the row are an exact match of all the values in another row.
To delete duplicate rows, you filter a list for unique rows, delete the
original list, and then replace it with the filtered list. The original
list must have column headers.

Caution   Because you are permanently deleting data, it's a good idea
to copy the original list to another worksheet or workbook before using
the following procedure.

   1. Select all the rows, including the column headers, in the list
you want to filter.

      HideTip
      Click the top left cell of the range, and then drag to the bottom
right cell.
   2. On the Data menu, point to Filter, and then click Advanced
Filter.
   3. In the Advanced Filter dialog box, click Filter the list, in
place.
   4. Select the Unique records only check box, and then click OK.

      The filtered list is displayed and the duplicate rows are hidden.
   5. On the Edit menu, click Office Clipboard.

      The Clipboard task pane is displayed.
   6. Make sure the filtered list is still selected, and then click
Copy Copy button.

      The filtered list is highlighted with bounding outlines and the
selection appears as an item at the top of the Clipboard.
   7. On the Data menu, point to Filter, and then click Show All.

      The original list is re-displayed.
   8. Press the DELETE key.

      The original list is deleted.
   9. In the Clipboard, click on the filtered list item.

      The filtered list appears in the same location as the original
list.

0
10/6/2005 6:54:06 PM
excel 39879 articles. 2 followers. Follow

9 Replies
523 Views

Similar Articles

[PageSpeed] 22

Maybe Debra Dalgleish's instructions will prove useful:

http://contextures.com/xladvfilter01.html#FilterUR

greg.mouning@yale.edu wrote:
> 
> Hi,
> 
> Below are instructions on how to "delete duplicate rows from a list in
> Excel". I learned about this tip from the Microsoft Office Assistant
> website. However, each time I try step 4, my list is not filtered and
> no records are hidden. Is there a secret I am missing to make this
> work?
> 
> Thanks for your help!
> 
> -Greg
> 
> http://office.microsoft.com/en-us/assistance/HA010346261033.aspx
> Applies to
> Microsoft Office Excel 2003
> Microsoft Excel 2002
> 
> A duplicate row (also called a record) in a list is one where all
> values in the row are an exact match of all the values in another row.
> To delete duplicate rows, you filter a list for unique rows, delete the
> original list, and then replace it with the filtered list. The original
> list must have column headers.
> 
> Caution   Because you are permanently deleting data, it's a good idea
> to copy the original list to another worksheet or workbook before using
> the following procedure.
> 
>    1. Select all the rows, including the column headers, in the list
> you want to filter.
> 
>       HideTip
>       Click the top left cell of the range, and then drag to the bottom
> right cell.
>    2. On the Data menu, point to Filter, and then click Advanced
> Filter.
>    3. In the Advanced Filter dialog box, click Filter the list, in
> place.
>    4. Select the Unique records only check box, and then click OK.
> 
>       The filtered list is displayed and the duplicate rows are hidden.
>    5. On the Edit menu, click Office Clipboard.
> 
>       The Clipboard task pane is displayed.
>    6. Make sure the filtered list is still selected, and then click
> Copy Copy button.
> 
>       The filtered list is highlighted with bounding outlines and the
> selection appears as an item at the top of the Clipboard.
>    7. On the Data menu, point to Filter, and then click Show All.
> 
>       The original list is re-displayed.
>    8. Press the DELETE key.
> 
>       The original list is deleted.
>    9. In the Clipboard, click on the filtered list item.
> 
>       The filtered list appears in the same location as the original
> list.

-- 

Dave Peterson
0
petersod (12005)
10/6/2005 7:26:22 PM
Should work if you have pre-selected the range to filter and you indeed have
duplicates.

Are you sure you have duplicates?


Gord Dibben Excel MVP

On 6 Oct 2005 11:54:06 -0700, greg.mouning@yale.edu wrote:

>Hi,
>
>Below are instructions on how to "delete duplicate rows from a list in
>Excel". I learned about this tip from the Microsoft Office Assistant
>website. However, each time I try step 4, my list is not filtered and
>no records are hidden. Is there a secret I am missing to make this
>work?
>
>Thanks for your help!
>
>-Greg
>
>http://office.microsoft.com/en-us/assistance/HA010346261033.aspx
>Applies to
>Microsoft Office Excel 2003
>Microsoft Excel 2002
>
>A duplicate row (also called a record) in a list is one where all
>values in the row are an exact match of all the values in another row.
>To delete duplicate rows, you filter a list for unique rows, delete the
>original list, and then replace it with the filtered list. The original
>list must have column headers.
>
>Caution   Because you are permanently deleting data, it's a good idea
>to copy the original list to another worksheet or workbook before using
>the following procedure.
>
>   1. Select all the rows, including the column headers, in the list
>you want to filter.
>
>      HideTip
>      Click the top left cell of the range, and then drag to the bottom
>right cell.
>   2. On the Data menu, point to Filter, and then click Advanced
>Filter.
>   3. In the Advanced Filter dialog box, click Filter the list, in
>place.
>   4. Select the Unique records only check box, and then click OK.
>
>      The filtered list is displayed and the duplicate rows are hidden.
>   5. On the Edit menu, click Office Clipboard.
>
>      The Clipboard task pane is displayed.
>   6. Make sure the filtered list is still selected, and then click
>Copy Copy button.
>
>      The filtered list is highlighted with bounding outlines and the
>selection appears as an item at the top of the Clipboard.
>   7. On the Data menu, point to Filter, and then click Show All.
>
>      The original list is re-displayed.
>   8. Press the DELETE key.
>
>      The original list is deleted.
>   9. In the Clipboard, click on the filtered list item.
>
>      The filtered list appears in the same location as the original
>list.

0
Gord
10/6/2005 7:34:04 PM
Hi Dave,

Thanks for your prompt reply. I'll give it a try.

-Greg

0
10/6/2005 7:41:38 PM
Hi Gord,

Yes, below is an excerpt of my duplicate data:

PY Donation	D_N	City	State	Zip
$0.00	N	Apache Junction	AZ	85220
$0.00	N	Gilbert	AZ	85234
$0.00	N	Green Valley	AZ	85614
$0.00	N	Mesa	AZ	85207
$0.00	N	Prescott	AZ	86305
$0.00	N	Berkeley	CA	94705
$0.00	N	Cardiff By The Sea	CA	92007
$0.00	N	Carson	CA	90745
$0.00	N	Claremont	CA	91711
$0.00	N	San Pedro	CA	90731
$0.00	N	Yountville	CA	94599
$0.00	N	Boulder	CO	80302
$60.00	D	Ansonia	CT	06401
$0.00	N	Ansonia	CT	06401
$0.00	N	Ansonia	CT	06401

As you can see the duplicate content is in columns.

Regards,
Greg

0
10/6/2005 7:48:39 PM
Greg

Which column are you filtering on?

If all, the only entire row duplicates are at rows 15 and 16.


Gord

On 6 Oct 2005 12:48:39 -0700, greg.mouning@yale.edu wrote:

>Hi Gord,
>
>Yes, below is an excerpt of my duplicate data:
>
>PY Donation	D_N	City	State	Zip
>$0.00	N	Apache Junction	AZ	85220
>$0.00	N	Gilbert	AZ	85234
>$0.00	N	Green Valley	AZ	85614
>$0.00	N	Mesa	AZ	85207
>$0.00	N	Prescott	AZ	86305
>$0.00	N	Berkeley	CA	94705
>$0.00	N	Cardiff By The Sea	CA	92007
>$0.00	N	Carson	CA	90745
>$0.00	N	Claremont	CA	91711
>$0.00	N	San Pedro	CA	90731
>$0.00	N	Yountville	CA	94599
>$0.00	N	Boulder	CO	80302
>$60.00	D	Ansonia	CT	06401
>$0.00	N	Ansonia	CT	06401
>$0.00	N	Ansonia	CT	06401
>
>As you can see the duplicate content is in columns.
>
>Regards,
>Greg

0
Gord
10/6/2005 8:01:06 PM
Gord, 

I'm filtering on the City and State (columns 3 and 4).

-Greg

0
10/6/2005 8:10:34 PM
>$0.00 N Ansonia CT 06401
>$0.00 N Ansonia CT 06401

The last 2 records/rows are the only duplicates that I see in the list. They 
are the only ones where all fields in one row match all fields in another 
row (which is the definition of a duplicate/unique entry).

I don't know why you see both when you filter for unique records. Are you 
sure both values are 0? Any chance one of them is 0.0001 but only displays 
as 0.00? That could make it unique and allow both to be displayed.

HTH,
-- 
George Nicholson

Remove 'Junk' from return address.


<greg.mouning@yale.edu> wrote in message 
news:1128628119.744590.154230@g49g2000cwa.googlegroups.com...
> Hi Gord,
>
> Yes, below is an excerpt of my duplicate data:
>
> PY Donation D_N City State Zip
> $0.00 N Apache Junction AZ 85220
> $0.00 N Gilbert AZ 85234
> $0.00 N Green Valley AZ 85614
> $0.00 N Mesa AZ 85207
> $0.00 N Prescott AZ 86305
> $0.00 N Berkeley CA 94705
> $0.00 N Cardiff By The Sea CA 92007
> $0.00 N Carson CA 90745
> $0.00 N Claremont CA 91711
> $0.00 N San Pedro CA 90731
> $0.00 N Yountville CA 94599
> $0.00 N Boulder CO 80302
> $60.00 D Ansonia CT 06401
> $0.00 N Ansonia CT 06401
> $0.00 N Ansonia CT 06401
>
> As you can see the duplicate content is in columns.
>
> Regards,
> Greg
> 


0
JunkGeorgeN (154)
10/6/2005 8:16:15 PM
Greg

That should return 14 values.

Rows 15 and 16 will be filtered out.


Gord Dibben Excel MVP

On 6 Oct 2005 13:10:34 -0700, greg.mouning@yale.edu wrote:

>Gord, 
>
>I'm filtering on the City and State (columns 3 and 4).
>
>-Greg

0
Gord
10/6/2005 8:24:52 PM
Gord,

For some reason it seems I have to click on "Auto Filter" while my City
and State columns are selected before anything is filtered. Not sure
why this is necessary but things appear to be working now. Thanks all
for your assistance.

-Greg

0
10/6/2005 9:18:01 PM
Reply:

Similar Artilces: