+ Reply to Thread
Results 1 to 11 of 11

Removing Duplicates from a list

  1. #1
    Peo Sjoblom
    Guest

    Re: 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



  2. #2
    Biff
    Guest

    Re: Removing Duplicates from a list

    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




  3. #3
    JohnGuts
    Guest

    Re: Removing Duplicates from a list

    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

    >
    >
    >


  4. #4
    Biff
    Guest

    Re: Removing Duplicates from a list

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

    >>
    >>
    >>




  5. #5
    Biff
    Guest

    Re: Removing Duplicates from a list

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

    >>
    >>
    >>




  6. #6
    JohnGuts
    Guest

    Removing Duplicates from a list

    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

  7. #7
    Peo Sjoblom
    Guest

    Re: 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



  8. #8
    Biff
    Guest

    Re: Removing Duplicates from a list

    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




  9. #9
    JohnGuts
    Guest

    Re: Removing Duplicates from a list

    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

    >
    >
    >


  10. #10
    Biff
    Guest

    Re: Removing Duplicates from a list

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

    >>
    >>
    >>




  11. #11
    Biff
    Guest

    Re: Removing Duplicates from a list

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

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1