[SOLVED] Delete Duplicates

I'm not totally new to Excel but I only use it to manipulate client data. I
need to delete duplicate clients. Not just the duplicate but the original
too. Is it possible without manually deleting? There's over 13,000 client
in this list and I don't have that kind of time.

Good evening Lauren

You can download my free add-in from www.dom-and-lis.co.uk. This has a duplicate manager function that should help you out with what you need.

HTH

DominicB

So if client10 occurs twice you want to delete both occurrences?

Assuming the clients are in the same column, assume they start with a header
named clients in A1 and their names going from A2:A13000 something,

in C2 put

=COUNTIF($A$2:$A$13100,A2)=1


leave C1 blank,

select A1:A13100 by typing the same in the name box above column A, do
data>filter>advanced filter,
the range should be there, select copy to another location and click for
example H1, in the criteria range put

$C$1:$C$2



click OK


This will do what you want, if you have duplicates but only want to filter
the extra occurrences, do the same filter but don't use any criteria just
check unique records only


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Lauren" <[email protected]> wrote in message
news:[email protected]...
> I'm not totally new to Excel but I only use it to manipulate client data.
> I
> need to delete duplicate clients. Not just the duplicate but the original
> too. Is it possible without manually deleting? There's over 13,000
> client
> in this list and I don't have that kind of time.

I like these methods....they don't involve inserting helper columns or
copying thousands of formulas:


For a list in A10:A10000, with the column heading in A10

Method 1 - Delete dups:
A1: DupTest
A2: =COUNTIF($A$10:$A$10000,A11)>1

Select A10:A10000
<Data><Filter><Advanced Filter>
Uncheck: Copy to another location
Uncheck: Unique records only
List Range: (already selected $A$10:$A$10000)
Criteria Range: $A$1:$A$2
Click the [OK] button

Once the data is filtered...select the visible cells under A10
Press the [Delete] key (a filtered list only allows you to delete entire rows)
Done

Or
Method 2 - copy non-dups to another location:
A1: NonDupTest
A2: =COUNTIF($A$10:$A$10000,A11)=1

C10: (the same column heading as A10)

Select A10:A10000
<Data><Filter><Advanced Filter>
CHECK: Copy to another location
Uncheck: Unique records only
List Range: (already selected $A$10:$A$10000)
Criteria Range: $A$1:$A$2
Copy to: $C$10
Click the [OK] button

That will copy all unique non-duplicated records to the cells under C10
Then you can delete the original list

Note the Dollar Signs in the above formulas

Do either of those help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Lauren" wrote:

> I'm not totally new to Excel but I only use it to manipulate client data. I
> need to delete duplicate clients. Not just the duplicate but the original
> too. Is it possible without manually deleting? There's over 13,000 client
> in this list and I don't have that kind of time.

hi try to use advance filter
go to data / filter / advance filter / click copy to another location /
answer the list range e.g. highlight the list / answer copy to e.g. b1 / and
check unique records only

you can chat me at [email protected]

"Lauren" wrote:

> I'm not totally new to Excel but I only use it to manipulate client data. I
> need to delete duplicate clients. Not just the duplicate but the original
> too. Is it possible without manually deleting? There's over 13,000 client
> in this list and I don't have that kind of time.