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
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
=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.
This is great thanks,
But is there any way that it will only show the duplicates and not the orginal
Thanks
FBF
Okay, try:Originally Posted by djfatboyfats
Please Login or Register to view this content.
copied down. This first record will NOT be flagged, but any duplicates thereafter will.
No still coming up with 4 entrys instead of 2
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?
Can you attach a sample worksheet (zipped XL2003 or earlier) file showing this result.Originally Posted by djfatboyfats
If you're only looking to see if the item in G2 exists at all in column F, then you want thisOriginally Posted by Jwalters
=IF(COUNTIF(F:F,G2)>0,"Duplicate Exists",0)
Attached
Thanks
FBF
You hadn't entered the formula correctly in J4....
It should be:copied down.Please Login or Register to view this content.
See attached.
Thank you so much. You saved me a few hours of work.
Originally Posted by Jwalters
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..
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?Originally Posted by djfatboyfats
If so, then we have to go to Sumproduct for multiple criteria....
something like:Please Login or Register to view this content.
No this dosent seem to work, please find attached
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","")
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.
Originally Posted by djfatboyfatsPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks