Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 4 of 6 FirstFirst ... 23456 LastLast
Results 31 to 40 of 57

Thread: Duplicates

  1. #31
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 17:25, Ricky Morris wrote:
    OK I'm lost. I have Jan, Feb... etc in cells A1 thru A13. Which cell(s) should I put this formula to display the unique values.
    Hi Ricky,

    With Zacemmel's formula, would type =A1 in cell B1 and his formula in B2 and copy down the list. For the one I posted, type it in B1 and copy down the list.

    Bye,
    Jay

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

    Default

    Zacemmel, this is pretty good. I'm impressed. However, I used the following test data and the formula yeilded incorrect result.

    A1 jan
    A2 feb
    A3 mar
    A4 apr
    A5 may
    A6 jun
    A7 jul
    A8 aug
    A9 sep
    A10 oct
    A11 nov
    A12 dec
    A13 jan
    A14 ricky

    The last unique cell, A14, was not displayed by the formula.
    It's never too late to learn something new.

    Ricky

  3. #33
    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

    You have to extend the range

  4. #34
    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

    Zac,

    extending the range as per your suggestion to the flaw posted by Ricky :

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

    does not work

    "Ricky" does not show up as a unique entry.
    Neither do any items in the list that proceed a numeric duplication, as I pointed out earlier.....

    try replacing "feb" with "111"

    then replace "jul" with "111"

    everything below *will* disappear.

    I hope this filters through... you don't seem to want to understand !!

    good luck


  5. #35
    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 understand my own formula. The range HAS to be full. If the range specified in the formula isn't full, it will not work. If the range is full, my formula works. You people just aren't using it correctly.

  6. #36
    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

    Here's another approach...

    See my posting at http://www.mrexcel.com/board/viewtop...c=7253&forum=2

  7. #37
    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

    The purpose of my post is to get rid of duplicates without using VBA, sorting, or filtering without getting spaces between records.

  8. #38
    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

    Zac,

    it's a wonderful formula, I like it.

    Alas, it doesn't quite work.

    I just filled my range per my example above and it fails.

    Would you like to post an example range that does work so that *we* can see where *us people* are going wrong ?

    Full marks for the time and trouble you've invested.... let's finish this off for posterity.

    Chris

  9. #39
    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

    On 2002-05-03 14:54, zacemmel wrote:
    The purpose of my post is to get rid of duplicates without using VBA, sorting, or filtering without getting spaces between records.
    Right... I didn't use VBA, AutoFilter, or the Data | Sort menu command. It works effortlessly... even on 10,000 rows.

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

    Default

    Here's a solution taken from John Walkenbach's Excel 2000 Formulas book.

    The source list containing the duplicates is named data. This is a multi-cell array. It should be entered at once in a range with the same number of rows as "data". Like all arrays it should be entered by pressing Ctl-Shift-Enter together.

    =INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))
    It's never too late to learn something new.

    Ricky

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
  •