Thanks Thanks:  0
Likes Likes:  0
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 57

Thread: Duplicates

  1. #1
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have seen several posts on the forum regarding duplicates. Most peope resort to UDFs and VBA to remove duplicates. That isn't neccesary. You can remove duplicates with the functions that are built-in to Excel.

    Check out the formula I created below:

    =IF(ROW()=ROW($A$1),$A$1,IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))))

    [ This Message was edited by: zacemmel on 2002-05-02 11:02 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What a cluster! How in the world did you come up with that one?

    Good job if it works.

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HELLO THERE ZACEMMEL

    PLEASE EXPLAIN TO ME HOW ARE THE DUPLICATES
    REMOVED WITH THIS FORMULA?

    REGARDS

    ANDREAS

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK I'll bite. Where do you put this jewel??
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm stumped at why you think this formula (or any formula) would remove duplicates since formula do not modify the contents of the spreadsheet they only return a result.
    It's never too late to learn something new.

    Ricky

  6. #6
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello there

    I Agree with Ricky

    Formulas don't remove duplicates !!!

    Regards

    Andreas

  7. #7
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It doesn't remove duplicates from the existing list. It removes duplicates from the new list that it creates. It works, trust me.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It does work and is amazing stuff I think.

    RET79

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Zacemmel,

    which cell would this start in ?

    and which range would have the potential list of duplicates ?

    I was just trying to test this impressive formula but couldn't quite get the right ranges

    thanks
    Chris

  10. #10
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Zacemmel

    I would like more details on this formula please.


    Andreas

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

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
  •