Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to find duplicates (2000)

    I got this formula from this forum. =IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A9<>"")*R OW(A2:A9)))),INDIRECT("A2:A"&(MAX((A2:A9<>"")*ROW( A2:A9))))))>1,"Duplicates","No Duplicates")

    I put some numbers in cells A1:A9 like 123455671 just to test the formula
    I copied the formula (from Word where I pasted it) into the formula bar and did control-shift-enter because it is an array
    I copied it down and get all duplicates and obviously 2 3 4 6 and 7 do not have duplicates
    One time it did work but I have been trying and trying and it won't work again. I would like to use this formula but I don't know why it worked once and now it doesn't -stumped!!

    Am I doing something wrong?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to find duplicates (2000)

    The formula is not one to be copied down the rows. It is a single formula to look for any duplicates (at all) in the list. 5 is a dup so the formula says "Duplicates". if you change the 5 to an eight it will say "No duplicates".

    It does not check individual ones. Here is one that does. Add it to B1:
    <pre>=IF(COUNTIF(A:A,A1)>1,"Duplicates","No Duplicates")</pre>

    This can be copied down to give whether and individual item is duplicated elsewhere in the list. It is a normal function not an array.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to find duplicates (2000)

    Thanks - that works fine but I did copy that other formula down and it did work. It said "no duplicates" at the first occurance and then indicated duplicate at subsequent occurances of the number.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to find duplicates (2000)

    That doesn't sound like the first formula worked, it produced the same answer for everything after the first row. That is the problem with the first formula, it can't be copied down, it has to be rewritten for each row it is used on.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to find duplicates (2000)

    What do you want the formula to do? I assumed that you wanted to look at each value and determine if there was more than 1. (hence my formula).

    The one you listed, does not do this. It is designed to compare each value in the range to determine if the the range has ANY duplicates. If that is what you want,There is no need to copy it down the rows (it will not work correctly copying it down the rows, anyway: it is not "designed" to be copied.

    If it did work on occasion, it was because the dataset was structured odd, not that the formula worked. It will most likely not give the correct answer if copied (though on occasion it will)

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to find duplicates (2000)

    Another thought, If you want to only indicate, true DUPES (the first is unique, the rest are dupes, try this:

    <pre>=IF(COUNTIF(A$1:A1,A1)>1, "Duplicate","Unique")</pre>


    This can be copied down the rows or into other columns.

    Steve

Posting Permissions

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