Removing Duplicates from a list

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


--
Regards,

Peo Sjoblom

(No private emails please)


"JohnGuts" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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


--
Regards,

Peo Sjoblom

(No private emails please)


"JohnGuts" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

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


--
Regards,

Peo Sjoblom

(No private emails please)


"JohnGuts" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>