Deleting Duplicate Data in a Column.

[Greg]

Hi,

I am trying to delete duplicate data in a column.
All of the data is in column A and has over 5000 rows.
What is unique about the data, is duplication of it is not the same,
please see the example below.

ABQ - Albuquerque, NM, USA - Albuquerque International Airport
ABQ - Albuquerque, NM, USA Albuquerque International Airport
ABR - Aberdeen, SD, USA - Aberdeen Regional Airport
ABR - Aberdeen, SD, USA Aberdeen Regional Airport
ABS - Abu Simbel, Egypt - Abu Simbel
ABS - Abu Simbel, Egypt Abu Simbel
ABX - Albury, New South Wales, Australia - Albury
ABX - Albury, New South Wales, Australia Albury

Is there a formula or macro that I can use to remove the duplicate
information where I can have a single line of data and also remove the blank
rows that had the duplicate data?

Thanks,
Greg

 

[Sandy Mann]

You could use Advanced Filter to copy a unique list which would remove all
the duplicates

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Greg]

Hi Sandy,

I Used Data/Filter/Advanced Filter:
I selected the range which is $A$1:$A$5549.
I'm not sure what the Criteria range should be, or if I need to checkmark
the box for
Unique records only.


Thanks,
Greg

 

[Sandy Mann]

My apologies, I failed to notce that your *duplicates* were not identical.
If the difference is always the second hyphen then in a *helper* column to
the right of the data, (insert a Temporary column if necessary), enter:

(I would recommend that you try this on a COPY of yourdata first)

=TRIM(SUBSTITUTE(A2,"-","",2))

where A2 is the first cell of data then add th label above the formulas.
Now double left-click on the fill handle of the above forumula which will
copy the formula down to the end of the data

With the formulas still highlighted Copy > Paste Special and paste back over
the same range that the formulas are in. Now delete your original data and
then do the Advanced filter selecting *Copy to another location* and *Unique
records only* Leave the *Criteria range* empty and select the first cell or
where the original data was as the *Copy to* range.

When you are confident, do it on your real data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Greg]

I got everything to work perfectly. Thanks for your help.

Greg

 

[Sandy Mann]

Glad that you got it all worked out and my apologies once more for not
reading your OP properly

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Juliet]

Hi,
Is there a way to automatically extract duplicate?

The duplicate datas are in my working sheet.
And would like to have another worksheet to list the unique data. This
worksheet will automatically extract duplicate & update the list whenever I
enter a new data in the working sheet.

Please advice how I can do it.

Thank you
Rgds
Juliet

 

[Sandy Mann]

I think that you really should have started a new thread with more detail of
how your data is laid out and what exactly you want extracted.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk