Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding non-unique numbers (97 SR-2)

    Hopefully this is a simple question !

    I have a column of 3500 entries including some 600-700 pairs, how can I find all the pairs/separate them from the rest?
    the format is like this
    st1211435
    st1211437
    st1211437

    Many thanks
    Gary

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding non-unique numbers (97 SR-2)

    Assuming the column with the entries is column A, starting from row 2 (one header line):

    Add a column with this formula:

    =SUMIF($a$2:$A$3501,A2)

    Now sort your data on the new column, making sure all columns are selected. You will have all entries that occur once together, all that occur twice together, etcetera.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding non-unique numbers (97 SR-2)

    Pieterse

    Thanks for your reply - but am I missing something?
    The attached file has 100 entries set up in the style you suggest and I don't get the double entries all together

    Your help is appreciated

    Gary
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding non-unique numbers (97 SR-2)

    Jan Karel Pieterse is not online right now; he must have meant COUNTIF instead of SUMIF.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding non-unique numbers (97 SR-2)

    Of course I meant COUNTIF.

    Thanks Hans.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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