How to check for duplicates in a column?
I am trying to alert an user from typing an already existing entry in a column. My Column will have values like
kat-1
kat-2
Amp-3
Amp-1
Below code here is checking for duplicates but it is doing by letter. For example, if the user were to enter 'k' it displays a duplicate entry message. But, I would like it to display the duplicate message for full text like "Kat-1"," Amp-1" etc.
Can anyone help with this problem?
Please Login or Register to view this content.
Thanks,
excelkann
I used your code in a blank workbook typing in names (from your example) and duplicating them and the code did give the message, however, I did not get the message if I typed a letter like 'k' as in your example. I even tried it with 'A' and didn't get the message.
I did notice that if there is a duplicate in column A and the duplicate is not removed, you will get the message until the duplicate is removed, no matter what is typed in a new cell. Is it possible there is still some duplicates somewhere in the column?
Can you attach your workbook (desensitized) with your code?
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Re: How to check for duplicates in a column?
Sheet 1 in the attached macro contains buttons at the top of columns A,B,C& E. All of these headers have the same userform associated with it. Click on any of these to open the
userform and type 'K'for base product name and you should see a "duplicate entry" message. Essentially, I would like a duplicate entry message when full kat-1 is entered by user.
Another strange thing is I am seeing a duplicate message, even when 'L' (which is not present in column A ) is entered. So, I am not sure if there is anything wrong with this code.
Regards,
excelkann
Ok, now that I see that you are using a UserForm, I see the problem you are having. I haven't come up with a solution yet, but I'm working on it when I can.
Have you considered using conditional formatting for that column?
With the code below, I have the program to not display the duplicate entry message as soon as a single letter is entered.
The program is expected to display a duplicate entry message only when an exact match is entered. Currently, if the column contains 'kat-1' and the userinputs 'kat-2', the program is seeing 'kat-2' as a duplicate entry. While it should see only ' kat-1 ' as duplicate entry.Please Login or Register to view this content.
@gmr4evr1- I need this problem to be fixed before tomorrow, I would appreciate it, if you can find a solution to it before then.
Regards,
excelkann
Someone else may have to jump on this one because I'm not sure I'll have it figured out before tomorrow with work and all.
Can anyone else take a look at this.
Thanks,
excelkann
The Code above is checking for duplicates. The Only issue is that it is seeing the immediate entry from user as a duplicate. For example, when user types in 'kat-1', it gets an update into column A and later gets compared to itself in the for loop. Any idea on how to avoid this?Please Login or Register to view this content.
Thanks,
I might have stumbled onto something here, give it a try
I combined your last code with your 1st one and I think we might be getting closer.Please Login or Register to view this content.
@gmr4evr1- Thanks for the code. I am using a similar code in my program like below.
End SubPlease Login or Register to view this content.
Regards,
excelkann
Nice job...it's a bit cleaner than the code I provided.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks