• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for Duplicate prevention

SHUN S

New Member
Hi All,
I'm a newbie to this forum.

I need some excel formula to prevent duplicate cells in my excel file.

Case: I have one excel sheet with one column containing certain cell occupied with data (Eg: A01 to A30), Now i'm try to paste some more data in the consecutive cells (Eg: A31 to A40). The data i'm pasted second (Eg: A31 to A40) may contain some data from first pasted data(Eg: A01 to A30). I need to remove the duplicates. In this case after pasting 2 datas i can remove duplicates. But i want to prevent from pasting of duplicate data..i.e. while pasting the second set of data, a pop window should appear and tell me "these data are already present" etc..

could any one help me regarding this?

Thanks in Advance.

SHUN S
 
Shun

Firstly, Welcome to the Chandoo.org Forums

Assume that you are entering data in Column D
Select Column D
Goto Data, Data Validation, Data Validation
Allow: Custom
Formula: =Countif(D:D,D1)=1
upload_2014-7-22_13-27-24.png

You can add an Input and error messages on the appropriate Tabs
 
sir hui,

the method you have mentioned is for single column, what if i want to prevent the duplicate entry for the whole sheet?
 
Hi Hui,
Thanks 4 yr reply.
I really helps only when the data are entered one by one...
If i copy a group of cells and paste it, it does not work

Thanks 4 yr effort and time.

Looking forward for help.

Thanks,
SHUN
 
Hi Hui,
Thanks 4 yr effort.

Im getting error of "A named range you specified cannot be found".

SHUN
 
Hi Hui,
Thanks 4 yr effort.

Im sry...Now also when i copy and paste the data...no pop up..
pls help

SHUN
 
sir hui,

in your second given formula you fixed the cell $A$1, for that i can put the data only in cell A1 not in any other cell, but what i need to put the data in any cell in a sheet but prevent the duplicate entry. please help sir....
 
Hi Shun,

This is a known problem, with data validation + Copy & Paste. If you paste a value it overrides your DV rule. Even it delete the rule. If you do paste special than Value than also the rule will not throw any pop-up although this time the rule remain intact. So what you can do paste only value and than select Circle INVALID data to know the dupes entry.

Regards,
 
Back
Top