Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Scenario: Column A1 through A100 contains a list of names, some of which are duplicated. What I would like to do is in column B list only the names in column A, but only list them once if they are duplicated. Any ideas?
    Thanks in advance.
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Insert a column header (field name) in A1, and duplicate it in C1 (it's better not to use B1 now, you can delete column B later on).
    Click in A1 (or another cell in the list in column A).
    Select Data | Filter | Advanced Filter...
    Click "Copy to another location".
    Tick the check box "Unique records only".
    Click in the "Copy to:" box.
    Select C1.
    Click OK.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767544' date='26-Mar-2009 08:10']Insert a column header (field name) in A1, and duplicate it in C1 (it's better not to use B1 now, you can delete column B later on).
    Click in A1 (or another cell in the list in column A).
    Select Data | Filter | Advanced Filter...
    Click "Copy to another location".
    Tick the check box "Unique records only".
    Click in the "Copy to:" box.
    Select C1.
    Click OK.[/quote]

    Thanks Hans. Unfortunately, I do not know how to insert a column header or field name. Can you elaborate?
    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jlkirk' post='767551' date='26-Mar-2009 14:23']Thanks Hans. Unfortunately, I do not know how to insert a column header or field name. Can you elaborate?
    Thanks.[/quote]
    Click anywhere in row 1.
    Select Insert | Rows.
    Select A1.
    Enter an arbitrary title for the column, e.g. Name.
    Enter the same text in C1 (or copy/paste).

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767555' date='26-Mar-2009 08:35']Click anywhere in row 1.
    Select Insert | Rows.
    Select A1.
    Enter an arbitrary title for the column, e.g. Name.
    Enter the same text in C1 (or copy/paste).[/quote]

    Hans,
    When I do as you say, I get a replication.
    Please see file.
    Jeff
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You haven't entered the word Name in C1.
    You have specified a Criteria Range. You should leave this blank.
    You have specified B2:B25 as Copy to range. It should be C1.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767672' date='26-Mar-2009 16:17']You haven't entered the word Name in C1.
    You have specified a Criteria Range. You should leave this blank.
    You have specified B2:B25 as Copy to range. It should be C1.[/quote]

    Yep-you're right!
    Thanks

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jlkirk' post='767543' date='26-Mar-2009 21:02']Scenario: Column A1 through A100 contains a list of names, some of which are duplicated. What I would like to do is in column B list only the names in column A, but only list them once if they are duplicated. Any ideas?
    Thanks in advance.
    Jeff[/quote]

    another way is to sort your list and use this formula in B2 assuming you have Header in row 1

    =IF(A3<>A2,A2,"")

    HTH
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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