Little Help For An Excel Newbie

[jguetter]

Hey guys, looks like a real good site you got going here. Alright m
question is:

I work for a film company and im currently putting together a mas
mailing list in excel. Now i have over 7000 entries but at a quic
glance ive noticed a bunch of duplicate entries. I've done a fe
searches and researched for a way to have excel possibly delete th
duplicates for me or to even have duplicates highlighted so its eas
for me to manually delete them but will take time i dont have. Ca
anyone help me out with some tips or some formula's to help my proble
here and to help me put them to use?

Thanks ahead of time

Jo

 

[Frank Kabel]

Hi
see the following sites:
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[jguetter]

I've seen them sites before and it tells me nothing. Is there anythin
in english that i could follow step by step? This is all new to me
sorry

 

[Frank Kabel]

Hi
lets try it step by step (though i think Chip's site are quite good
structured):
1. Assumptions:
- you have a heading row 8row number 1)
- you have your data in column A (e.g. A2:A7000)

2. Formula approach:
- use column B as helper column
- put a header in cell B1 (e.g. put the word 'Duplicate' into cell B1)
- insert the following formula in B2
=IF(COUNTIF($B$2:$B2,B2)>1,"X","")
- copy/drag this formula down for all rows or even easier: Double click
on the lower right corner of the selected cell B2. This should copy the
formula down automatically
- Now goto row 1 and select cell A1
- goto 'Data - Filter - Autofilter'
- click on the drop-down listbox in cell B1 and choose 'X' to filter
all duplicates
- delete all duplicated rows
- goto 'Data - Filter' and uncheck 'Autofilter

3. There's also a macro on Chip's side wich you could easily use but if
this is a on-time operation probably the procedure from above will work
for you

 

[jguetter]

Well that helps clear up a few things but now its giving something abou
having a circular reference and doesnt do anything so maybe i just nee
to find someone to just come and do it for me. This is very confusin
for me. haha sorr

 

[Frank Kabel]

Hi
no this was my fault. change the formula
=IF(COUNTIF($B$2:$B2,B2)>1,"X","")

to
=IF(COUNTIF($A$2:$A2,A2)>1,"X","")

 

[jguetter]

Excellent it worked. Alright now im almost done. The duplicated row
now have an X next to them and it looks like there is a little mor
then anticipated. Is there a way to have excel delete them from here?
Thanks agai

 

[Gord Dibben]

You can use Data>Filter>Advanced Filter.

"Copy to a new location" and "Unique records only"

Filtering Tutorial at Debra Dalgleish's site.......
http://www.contextures.on.ca/tiptech.html

Gord Dibben Excel MVP

 

[jguetter]

Hey just wanted to let you know i got it. I figured out how to put th
X's next to the duplicated then i simply sorted them together and di
one large "delete" and cleaned out all of my duplicates. Thanks alo
for all your help!

Jon
H-Bomb Film

 

[Frank Kabel]

Hi
I described this procedure in my previous post (using filter and
deleting them manually)