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

Datafilteradvanced filer, copy to another location and unique records only


--
Regards,

Peo Sjoblom

(No private emails please)


"JohnGuts" wrote in message
...
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

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

"JohnGuts" wrote in message
...
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

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

"Biff" wrote:

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

"JohnGuts" wrote in message
...
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

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?

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

"JohnGuts" wrote in message
...
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

"Biff" wrote:

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

"JohnGuts" wrote in message
...
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

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

There should be no empty cells WITHIN the range:

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

Biff

"JohnGuts" wrote in message
...
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

"Biff" wrote:

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

"JohnGuts" wrote in message
...
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