+ Reply to Thread
Results 1 to 5 of 5

to find duplicate cell in a range

  1. #1
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    to find duplicate cell in a range

    Hi,

    I have a range of account code in a column and i would like to find out whether there are duplicate items or not. Which formula function is to use to detect it?

    Thanks in advance
    Last edited by mingali; 06-03-2010 at 08:05 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: to find duplicate cell in a range

    =COUNTIF(A$1:A1,A1)>1

    copied down, will enter TRUE when a duplicate of an already existing name comes up.

    Only the Duplicate(s) will have TRUE, not the first occurence.

    This way you can then filter for TRUE and delete those if desired.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    Re: to find duplicate cell in a range

    Hi NBVC,

    Would you please check what has gone wrong with the worksheet? Row 21 is true while there is no duplicate while row 39 is false when there are duplicates.

    What cause this issue?

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: to find duplicate cell in a range

    It was assumed you were starting at row 1, in your case, enter in C3:

    =COUNTIF(A$3:A3,A3)>1

    and copy down

  5. #5
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: to find duplicate cell in a range

    Or you can put this in C3 and drag down..

    =IF(MAX(COUNTIF(B3:B99,B3:B99))>1,"Duplicates","No Duplicates")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1