Preventing duplicate row entries

**kennethchu604** · 10-21-2009, 04:14 PM

Hello,

I am looking into creating a macro that would be prevent the entry of duplicate rows of data, within a worksheet. If the input text exists, there should be a warning message, with no option to leave the duplicated item.

I found the below coding in a different thread. Would it be possible to adjust the below coding to identify duplicate rows of data, from columns A to I? Please let me know if you require more information.

Thanks,
Ken

Code:

Please Login or Register  to view this content.

**DonkeyOte** · 10-22-2009, 02:50 AM

I guess my instinct (to keep things light-ish) would be to store the concatenation of A:I in another column - say IV ... use this as the basis for duplicate test.

Please Login or Register  to view this content.

Does that help ?

If you avoid the use of IV then the comparison could prove to be quite "expensive" given it will fire on each cell change in A:I range.

**kennethchu604** · 10-22-2009, 12:13 PM

Hello,

Thank you for your assistance. I created the below concatenation formula in column "IV" and copied it down the column. When a new entry row is processed, the concatenation formula will appear in "IV", for that specific row. However, when I process a duplicate row of data, no warning appears. Should I be using a different concatenation formula? Please let me know if you require more information.

=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2)

Thanks,
Ken

**DonkeyOte** · 10-22-2009, 12:18 PM

...I created the below concatenation formula in column "IV" ...

You don't need to enter anything into Column IV, the VBA Event will do that as and when content of A:I is altered
(in my post I was merely trying to flag the fact that column IV would be in use)

...when I process a duplicate row of data, no warning appears

check that you have placed the code correctly in VBEditor... it (Worksheet_Change) is a Worksheet level event and thus should reside in the appropriate Worksheet Object... to check that you have done this:

Right click on the tab against which you wish the code to be applied -> select View Code -> ensure the code provided previously appears in the resulting window (if not paste it into this window). Thereafter you must of course ensure the file is enabled for macros.

**kennethchu604** · 10-30-2009, 01:09 PM

Thank you for your help, I have come accross the following problems:

1) Checking the previously existing data vs newly entered data. It appears that no warning message is shown when a dulplicate row is processed for data that is already existing in the worksheet.

2) If we enter the information via the form module, could the check be applied to the newly entered data.

3) If duplicate infromation is processed, could the entire row be deleted, instead of the most recent cell entered

4) Could column F not be included in the check. Thus, the check should be only applied to columns A to E and G to I.

Thanks,
Ken