Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Display repeating items in another column only once?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have flight log speadsheet containing a couple of thousand records. One column contains the three letter aircraft registration. I want to dispay an alphabetical list of the individual registrations on another sheet. There are about 30 unique aircraft regos that repeat frequently through out the column.

    Essentially I need a way to find and display every item from one column in another column but only once. Preferably in alphabetical order.

    Can someone please tell me which formula I should use to achieve this.

    Source Result

    EBW EBQ
    EBQ EBS
    EBS EBW
    EBS EBY
    EBY ECC
    EBW
    EBW
    ECC
    EBY
    EBW
    ECC
    EBY

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you require a formula? Or, will a couple of commands suffice?

    [ This Message was edited by: Mark W. on 2002-03-18 15:47 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would prefer a formula so that it would be more automated. But now that I think of it a couple of command would probably suffice as I could no doubt make a macro to run them.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, suppose A1:A13 contains...

    {"aircraft regos "
    ;"EBW"
    ;"EBQ"
    ;"EBS"
    ;"EBS"
    ;"EBY"
    ;"EBW"
    ;"EBW"
    ;"ECC"
    ;"EBY"
    ;"EBW"
    ;"ECC"
    ;"EBY"}

    1. Select A1:A13 and choose the Data | Filter | Advanced AutoFilter... menu command.
    2. Click on Copy to another location.
    3. Enter B1 in the Copy to field.
    4. Check Unique records only and press [ OK ].
    5. Select column B:B and Sort the values to get...

    {"aircraft regos "
    ;"EBQ"
    ;"EBS"
    ;"EBW"
    ;"EBY"
    ;"ECC"}

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks thats excellent and will get me by nicely.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Roo and Mark

    I use this formula in B1 to display unique entries in column A. (needs sorting afterwards)
    =IF(COUNTIF($A$1:$A$33,A1)=1,A1,IF(AND(COUNTIF($A$1:$A$33,A1)>1,COUNTIF($A$1:A1,A1)=1),A1,""))

    Extend the A33 to as many rows as necessary
    regards
    Derek

    [ This Message was edited by: Derek on 2002-03-18 16:53 ]

  7. #7
    New Member
    Join Date
    Nov 2007
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This information was helpful, but I need to be able to filter and copy this list to another file - (or even another worksheet within the workbook would work) -- I have not been able to do this, beyond the same worksheet that you're filtering from.... can you help? thanks. Linda

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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