Formula for Duplicate prevention

[SHUN S]

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

[FDibbins]

Hi and welcome to the forum

You could use a helper column (column B, for instance), and then use the COUNTIF() function. You said your data starts in A1, so in B1, put this and copy down...

=IF(COUNTIF($A$1:A1,A1)>1,"Duplicate","")

Another way to remove duplicates is with excel's built-in Remove Duplicates button found under Data/Data Tools/Remove duplicates (save your file before you try this so that if you dont like the results, you can reload the file)

[jewelsharma]

Hi Shun S,
Welcome to Excel Forum.

To answer your query, unfortunately there is no built-in functionality in Excel that will check immediately pasted data with previously existing data and provide such a pop-up if duplicates exist. The only option is to remove duplicates after pasting (which you are already aware of).

However, what you desire maybe possible with VBA. How comfortable are you with VBA?
If you are aware of Worksheet_Change event; then you can create a code that will run when you paste the data 2nd time around; and check those values against the existing data to confirm whether or not there are any duplicates.

Try searching for the said event and look at some examples of how it can be used. Also, please share a copy of your workbook - showing some sample data and what you desire - makes it easier for forum members to assist you.
Good luck

[Saarang84]

Hi,

Welcome to the Forum.

It seems you need a conditional formatting formula in the appropriate column. As per your example, in column A, assuming you have 10000 rows of data, then you need to apply the below formula in conditional formatting and use some color for highlighting the duplicates :

Please Login or Register  to view this content.

[ashishmehra2010]

Hi Shun S,

Please check the attached file for Duplicate Prevention.

In column A unique numbers are entered in cell A1:A30. When you enter the duplicate number in A31:A40 you will get a message as per your requirement by using Data Validation.

Kindly go through & update if any further assistance is required.

Regards,
Ashish Mehra

[SHUN S]

Hi Mehra,
Thanks u very much 4 yr fast reply.

I need some more clarifications 4m u,
Yr file works fine only when the values are entered one by one.. For my case i will just copy and pasting the bunch of data, At that time it does not work out.
Could u plz xplain me how come pink color cells are highlighted (When i try for myself in separate sheet. it doesn't works).

Thanks 4 yr time and effort.

SHUN

[SHUN S]

Hi jewelsharma,
Thanks 4 yr kind reply.

I'm new to VBA..I have heard that people will be doing lot of automation in VBA.
If possible could u pls get me code 4 this.

THanks,
SHUN S

[SHUN S]

Hi All,
Could any one provide me solution for this case?

Thanks in Advance.

SHUN S

[Saarang84]

Hi,

My formula in post #4, can be used as an conditional formatting formula. When applied, this highlights the duplicates pasted into a column in the color that you specify in the conditional formatting. Refer attached workbook, I've used Red color. Once the data is pasted, the duplicates will be highlighted and can be removed manually later.

[SHUN S]

Hi Saarang84,
Thanks 4 yr effort and time.

I think it would help me a lot.

Kindly reveal the formula and process behind the RED highlight.

Thanks and regards,
SHUN

Hi,

My formula in post #4, can be used as an conditional formatting formula. When applied, this highlights the duplicates pasted into a column in the color that you specify in the conditional formatting. Refer attached workbook, I've used Red color. Once the data is pasted, the duplicates will be highlighted and can be removed manually later.

[Saarang84]

Hi Saarang84,
Thanks 4 yr effort and time.

I think it would help me a lot.

Kindly reveal the formula and process behind the RED highlight.

Thanks and regards,
SHUN

Hi,

The formula behind the red highlighting is as in my post #4, here data starts from cell A1 :

Please Login or Register  to view this content.

On Home tab strip, go to conditional formatting new rule, choose Use a Formula to determine which cells to Format option, and give this condition in the textbox below (after changing the column ranges accordingly as per your need). Then click Format button, keep Font style as Bold and color as White and in Fill Tab choose Red color and click OK twice. Now the formula is ready.

[jagadeesh.rt]

Hi SHUN S,

Try this code with example : Column A (Deletes duplicates in Column A (entire row).

Please Login or Register  to view this content.