+ Reply to Thread
Results 1 to 18 of 18

Formula for finding duplicates

  1. #1
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459

    Formula for finding duplicates

    I have excel sheets that will show a customer account number an ammount they need to pay and there full name and a date when the payment is expected, is there a formula that will find duplicates, if the same entry has been put in twice

    Thanks

    FBF

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    =IF(Countif(A:A,A1)>1,"Duplicate Exists","") copied down

    Checks to see if what is in A1 exists more than once in column A
    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
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    This is great thanks,

    But is there any way that it will only show the duplicates and not the orginal

    Thanks

    FBF

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by djfatboyfats
    This is great thanks,

    But is there any way that it will only show the duplicates and not the orginal

    Thanks

    FBF
    Okay, try:

    Please Login or Register  to view this content.

    copied down. This first record will NOT be flagged, but any duplicates thereafter will.

  5. #5
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    No still coming up with 4 entrys instead of 2

  6. #6
    Registered User
    Join Date
    12-20-2007
    Posts
    4
    I tried using this formula for a similar solution I need. I have two columns of data and I need to find if a number in the first column appears again in the second column (but is not in the same row). I changed the formula to this:

    =IF(COUNTIF(F:F,G2)>1,"Duplicate Exists","0")

    I thought that would show if the value in G2 appears anywhere in column F, but it only comes back as dupicate when the value in column G is zero. How should I adjust the formula?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by djfatboyfats
    No still coming up with 4 entrys instead of 2
    Can you attach a sample worksheet (zipped XL2003 or earlier) file showing this result.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by Jwalters
    I tried using this formula for a similar solution I need. I have two columns of data and I need to find if a number in the first column appears again in the second column (but is not in the same row). I changed the formula to this:

    =IF(COUNTIF(F:F,G2)>1,"Duplicate Exists","0")

    I thought that would show if the value in G2 appears anywhere in column F, but it only comes back as dupicate when the value in column G is zero. How should I adjust the formula?
    If you're only looking to see if the item in G2 exists at all in column F, then you want this

    =IF(COUNTIF(F:F,G2)>0,"Duplicate Exists",0)

  9. #9
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    Attached

    Thanks

    FBF
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You hadn't entered the formula correctly in J4....

    It should be:
    Please Login or Register  to view this content.
    copied down.

    See attached.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-20-2007
    Posts
    4
    Thank you so much. You saved me a few hours of work.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by Jwalters
    Thank you so much. You saved me a few hours of work.

    No problem...but please next time start your own thread..it is against forum rules... you can always reference another thread by linking it, if you want others to see a similar thread, etc..

  13. #13
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    That is excellant just what i wanted, but one last question is there a way to have this formula but with 2 types of critiria, i.e 2 columns, where would this fit into the formula

    Thanks

    FBF

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by djfatboyfats
    That is excellant just what i wanted, but one last question is there a way to have this formula but with 2 types of critiria, i.e 2 columns, where would this fit into the formula

    Thanks

    FBF
    So you want it to say duplicate if the item in A4 along with the item in say E4 is duplicated?

    If so, then we have to go to Sumproduct for multiple criteria....

    something like:
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    No this dosent seem to work, please find attached
    Attached Files Attached Files

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Can you tell me why it doesn't seem to work?

    You are comparing columns A and E, there are no duplicates with same items in cells within columns A and E that I can see.

    Also, to get rid of the duplicates in the blank range...just add an IF statement...

    e.g. =IF(SUMPRODUCT(--($A$4:$A4=A4),--($E$4:$E4=E4))>1,"Duplicate","")

  17. #17
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459
    Ok yeah sorry that was my fault, i didnt add data to one column.

    How do we get rid of the duplicates in the blank range, what if formula can we use?

    Sorry about this.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by djfatboyfats
    Ok yeah sorry that was my fault, i didnt add data to one column.

    How do we get rid of the duplicates in the blank range, what if formula can we use?

    Sorry about this.
    Please Login or Register  to view this content.

+ 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