Removing Duplicates from a list

J

JohnGuts

If I have a list (Column) of zip codes in column A that range from 68116 to
68184, and there are multiple entries for each zip code. I would like Column
B to look through the list and create a new list that takes out the duplicate
entries. Any ideas how I can do this? I want to be able to add new zip
codes to the list, and have the new list automatically update. Thanks for
any help. JohnGuts
 
P

Peo Sjoblom

Data>filter>advanced filer, copy to another location and unique records only


--
Regards,

Peo Sjoblom

(No private emails please)
 
B

Biff

Hi!

Here's a way to do this dynamically....

Create a dynamic named range for the zip codes in column A.

Assume the codes are in the range A1:An

Cell B1 must be empty.

To extract the unique codes in B2 on down, enter this formula using the key
combo of CTRL,SHIF,ENTER:

=IF(ISERROR(INDEX(rng,MATCH(0,INDEX(COUNTIF(B$1:B1,rng),,1),0))),"",INDEX(rng,MATCH(0,INDEX(COUNTIF(B$1:B1,rng),,1),0)))

Copy down to enough cells so that when you enter new unique codes they will
be "picked up" by the formula in column B.

Rng is the dynamic named range for the codes in column A.

All of this is contingent on the fact that there will be no empty cells
within the range of codes in column A.

Want to see a sample file with this implemented? Let me know how to contact
you.

Biff
 
J

JohnGuts

Thanks for the quick response. If my list of zip codes in in A1:A100, and I
add a new zip code in A101, will I have to change the formula to incorporate
the new cell? You said that there should be no empty cells in my range A. I
am trying to leave enough room to add new records and have the formula
automatically pick up the new zip. Thanks!

John
 
B

Biff

Hi!
If my list of zip codes in in A1:A100, and I
add a new zip code in A101, will I have to
change the formula to incorporate the new cell?
Click to expand...

If you use a dynamic named range as I suggest this will happen
automatically.

How to create a dynamic named range:

http://contextures.com/xlNames01.html#Dynamic

If your zips are in A1:A100 but only 10 of those entries are unique then the
formula in column B would only need to be copied to 10 cells but to allow
for new unique zips being added to column A you would want to copy the
formula to more than 10 cells. You might want to copy it to 25 cells.

Biff
 
B

Biff

Hi!
You said that there should be no empty cells in my range A. I
am trying to leave enough room to add new records and have the formula
automatically pick up the new zip
Click to expand...

There should be no empty cells WITHIN the range:

A1 = 12345
A2 = 54321
A3 =
A4 =
A5 = 23456
A6 = 65432

Biff
 
Top