How to Avoid Duplicate Entries in Excel Worksheet While Transferring Data from UserForm

Рет қаралды 49,115

10 жыл бұрын

How to Avoid Duplicate Entries in Excel Worksheet While Transferring Data from UserForm.
Complete VBA code available here: www.exceltrainingvideos.com/ho...

Пікірлер: 88

@kb180769 7 жыл бұрын

Dear Dinesh! Really great work. I had spent many hours to find a solution for this. You made things so simple. Please keep it up. God bless you....

@zatak3689 3 жыл бұрын

Everytime I get stuck at something, I look up for your tutorials first. And 9/10 times you have the solutions to my problems. Can't thank you enough!! You are great Sir!!💯💯

@Exceltrainingvideos 3 жыл бұрын

Thanks. Please share with your friends too.

@rayvanderheiden3565 2 жыл бұрын

Thank you so much for this and your other videos. I appreciate the pace of your tutorials. Your instructions are very clear and concise. I’m a beginner and wanted to avoid using VBA but your tutorials have given me a good introduction. Once again, thank you.

@Exceltrainingvideos 2 жыл бұрын

You are so welcome! Please share my KZfaq channel on Excel VBA with all your friends. Thanks.

@tonodurond6149 9 жыл бұрын

Thanks, i looked everywhere for this info! Great video!

@jackray816 3 жыл бұрын

thank you soooooo much, i think this is the only useful video for avoiding duplicates of VBA in KZfaq good luck

@Exceltrainingvideos 3 жыл бұрын

Glad it helped

@dr.muhammadafnantalib5941 2 жыл бұрын

Dinesh saab! excellent elaboration. God bless you.

@Exceltrainingvideos 2 жыл бұрын

Thank you!

@wivanw1 9 жыл бұрын

Hi, thanks a lot for your video, it helped me a lot. However, how can I edit my data without duplicating it (in the/a listbox)? I would like to re-populate the date in the userform.

@fluto829 9 жыл бұрын

Thank you so much for your video!!!!

@lazyreviewssupport9811 Жыл бұрын

11:00 why look in whole worksheet. Is it not sufficient to simply check in column where the ID is stored?

@toniaadams2338 6 жыл бұрын

This was quite useful thanks a million

@mielkew27 4 жыл бұрын

As always simple and easy to follow, what if we are looking for two columns instead of one? I'm trying to do something like reference number and revision. I need to control whether the revision already exists? Will be much better if a MAX value can be introduced to know what should be the next revision. But without MAX still very helpful just to find number and revision already existing. Kudos and I hope you can help me with this.

@Exceltrainingvideos 4 жыл бұрын

This VBA code will help: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes End Sub You can also use it without the worksheet event.

@wallywechs4694 3 жыл бұрын

I always find your materials superb

@Exceltrainingvideos 3 жыл бұрын

Thank you! All the best!

@karuppusamyn515 3 жыл бұрын

one of the important useful form. same practice file i got. thank you so much sir....👌🏻👌🏻

@Exceltrainingvideos 3 жыл бұрын

All the best!

@dashadow212 9 жыл бұрын

Will the data transfer to excell if you only fill out first name and last name but not the ID #?

@naseem035 8 жыл бұрын

Hi I already use data validation to create combo box entry and i want to prevent duplicate selection of entry.

@ramosastanos5318 4 жыл бұрын

Thank you very much! very helpful

@Exceltrainingvideos 4 жыл бұрын

You are welcome. Please share with your friends and colleagues.

@snaqvi009 8 жыл бұрын

Hi, This is very useful like your's others videos. I would like to know LastName's Value either then Cell's Address. Thank You.

@snaqvi009 8 жыл бұрын

Just I Found the answer of my above question in your others videos. I set below code for this, If you can more modify please do. Search = Me.Policy_Box1.Text Set FoundCell = wks1.Columns(7).Find(Search, LookIn:=xlValues, Lookat:=xlWhole) If Not FoundCell Is Nothing Then MsgBox "Data Exist!" & " Invoice Number Is " & FoundCell.Cells.End(xlToLeft).End(xlToLeft).End(xlToLeft).End(xlToLeft).Value & "-", vbOKOnly + vbCritical, "Warning" Exit Sub End If Scenario A I want to go all over to left cell. (Which I can little bit do by above code). Scenario B I I want to go (nearest)next left cell. (I would like to know syntax). Thank You.

@HichKack 8 жыл бұрын

thank a lot , very usefull

@karimzaki8631 9 жыл бұрын

thank you very much sir

@pancaka1 5 жыл бұрын

sir. i have data on other sheet2. how the procedure to prevent duplicate entries on sheet 1. txu

@cristianhaica9405 9 жыл бұрын

Hi Dinesh, Why do i get subscript out of range (9) ?

@KTsPridiction 7 жыл бұрын

hello Dinesh Sir, great trick.... please with me about one thing if i have submitted a entry with macros and submitted data have Customer ID now i have to submit again second record which is also have Customer ID now if Customer ID is duplicate then msg box should be open can you help me in this

@Exceltrainingvideos 7 жыл бұрын

Run a loop to check for duplicates or Search www.exceltrainingvideos.com

@jagdishshinde9233 6 жыл бұрын

Respected sir, can you please suggest me any book for excel vba user form..

@apekshitdhoke 8 жыл бұрын

Hi Dinesh, I also have the same query as that of Scarlet Leaner in the below comments. If I have to check duplicates not in 1 column but in multiple columns combined then how can we modify the code ? Kindly note my combination of multiple columns is as follows: Employee Name: Combo Box From Date: Microsoft Date & Time Picker Control 6.0 To Date: Microsoft Date & Time Picker Control 6.0 Leave Type: Combo Box Thanks in advance, appreciate your tutorials a lot :)

@OasisExcelMaster Жыл бұрын

You use & to join the cells contents .....e.g. txtbox1.value & txtbox2.value .....it works just fine. I assumed you are not a very basic user

@BestFriendsForeverTrue Жыл бұрын

Thanks

@masnawiahzainuddin9516 6 жыл бұрын

can you help me to prevent duplication in 2 different sheet............so if i entered data that already in column C in sheet 2 in sheet 1, the excel will prevent it.............please, hope someone have solution for my problem......sorry for bad english..

@chenchowangdi1823 4 жыл бұрын

Sir, Your videos are really helpful and i have managed to design many data entry forms for my office use. One problem i have now is when i click my SAVE DATA Command button my data gets saved in the data sheet but same data gets saved again and again each time i click the Save command button . Is there any way to avoid saving the same data repeatedly?.

@Exceltrainingvideos 4 жыл бұрын

The strategy shown in the following link will solve the problem: www.exceltrainingvideos.com/automate-backup-of-current-file-quickly/

@Shib_ghosh 10 жыл бұрын

Hi Sir, I want to create macro which can cut and paste the entire row to a different worksheet from the following table based on arguments. For example if type Jan or Feb or Mar in the target column then that particular row will copy to the worksheet named Jan/Feb/Mar and the empty row of the source sheet will be deleted. My Target Column Will be Month. The Table format is as follows:- Date Expense Amount Payment Status Month. Thank you so much sir. Waiting for you reply.

@ankkitgarg55 3 жыл бұрын

Hi Sir, if I have shown the display warning for duplicate entry in the form but after then if I have to kept the cursor in the duplicate entry text box which code line I have to put.

@Exceltrainingvideos 3 жыл бұрын

This Excel VBA tutorial will help: kzfaq.info/get/bejne/sNGFmsiS2ZbVnoE.html

@abdifatahabdullahihassan3866 6 жыл бұрын

Dear Teacher thanks for your effort i want to search duplicate id cards so how to make?

@Exceltrainingvideos 6 жыл бұрын

These website links will help: www.exceltrainingvideos.com/find-duplicates-in-two-different-worksheets-in-ms-excel/ www.exceltrainingvideos.com/?s=advanced+filter www.exceltrainingvideos.com/find-missing-data-using-countif-nested-in-if-function/ Or search www.exceltrainingvideos.com

@moaz7142 3 жыл бұрын

Thanks ❤💖💖💖

@Exceltrainingvideos 3 жыл бұрын

You're welcome 😊 Please share this channel on Excel VBA solutions with your friends too.

@nassimgaming4948 6 жыл бұрын

How to change the vba code to search for both id and name not only if id is duplicated Means if id + name is duplicate

@abhiabhiful 4 жыл бұрын

Sir plz tell me How to check my two textboxes have same entry and then only it avoids as duplicate entry else if one has different and one has same it allows

@Exceltrainingvideos 4 жыл бұрын

Use an IF condition. The following web link will guide: www.exceltrainingvideos.com/if-else-function-using-vba-in-excel/

@MunawarFairooz 5 жыл бұрын

Dear Sir , i have a userform with 3 text boxes ,while entering the data , i want to check this same entry already made or not , if already available in the particular sheet then not allow to enter , if the value is different (any of textbox) then allow to enter .,,, can you help do this coding

@Exceltrainingvideos 5 жыл бұрын

This link will guide: www.exceltrainingvideos.com/create-database-in-excel-quickly-and-easily/

@chenchowangdi1823 4 жыл бұрын

I also want to know if it is possible to avoid double entry on the same day or within certain period of time.

@Exceltrainingvideos 4 жыл бұрын

The code will always prevent duplicate entries.

@chenchowangdi1823 4 жыл бұрын

@@Exceltrainingvideos Thank you sir. In my case i want to restrict duplicate entries on the same day or in certain period of time (e.g within a week etc).

@ashishthakre4177 10 жыл бұрын

thanx sir

@kerznerk2 4 жыл бұрын

Hi sir. Grateful if you could reply to my query. I'd like to know if i want to get the opposite value of foundcell is Nothing. I want state that if ID is Found, then xxxx, else xxxx

@Exceltrainingvideos 4 жыл бұрын

Sub IfElse() If Range("A2") = "abc123" Then MsgBox "ID found" Else MsgBox "ID not found" End If End Sub

@karthikduggana5981 6 жыл бұрын

Dear sir i have dates and amounts in columns tomeny amounts in same day how to sum them with macro coding please give me the code

@Exceltrainingvideos 6 жыл бұрын

Use a 'for next' loop.

@KnowEverythin 6 жыл бұрын

what is the duplicity code if i check two rows( Date and shift)?

@Exceltrainingvideos 6 жыл бұрын

This link will help: www.exceltrainingvideos.com/if-function-2/

@saluma.m.el-harthy9265 3 жыл бұрын

The minutes I hear the your sound voice I know it's you Mr Perfect Excel

@Exceltrainingvideos 3 жыл бұрын

Thank you! Please share the Excel VBA tutorials with your friends too.

@saluma.m.el-harthy9265 3 жыл бұрын

@@Exceltrainingvideos really I appreciate you alot as I have actually finished my project via your working codes though some I have to change due to excel 's versions But one thing If you cold help me giving your email and I will my project as may be will help you to expand the challenge of codes

@freefiregang1275 4 жыл бұрын

Dear Sir, It could not work properly, there was showing msgbox the ID value is existing but when i click on ok then that value gone to save every time please help me.

@Exceltrainingvideos 4 жыл бұрын

This link to the VBA tutorial will help: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-worksheet-while-transferring-data-via-userform/ The web link also has a downloadable sample file for practice.

@SumeshTkBSNL 3 ай бұрын

Done ✅

@khawar9181 8 жыл бұрын

Sir here is my question that id column (1 row) ABN001,(2 row)ABN002,(3 row)ABN004 and so on when i put the id ABN003 it should adjust in third row automatically insert the row after second row and put the id abn003 before abn004 hopfully understand my question Regard

@Exceltrainingvideos 8 жыл бұрын

+Zafar Janjua This link might help: www.exceltrainingvideos.com/how-to-sort-excel-data-automatically-using-vba/

@vishalupadhyay1166 8 жыл бұрын

Hi Sir, are u providing any classes, i want to learn step by step , can u help me, my contact no. is 9990612035

@biswal999 7 жыл бұрын

Hi Sir, while running this Macro Program I am getting this error message "Run time error" "object required" Could you please help me solve this problem.

@Exceltrainingvideos 7 жыл бұрын

Check out this link: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-worksheet-while-transferring-data-via-userform/ You can also do a search at: www.exceltrainingvideos.com

@rajeevdixit7019 3 жыл бұрын

How to change this file with macro enabled

@KnowEverythin 6 жыл бұрын

sir, I am using and function but nt work.when I check one textbox and combo box.... if both value are same nd are in worksheet it will give error msg.. date and shift.... pls help

@Exceltrainingvideos 6 жыл бұрын

Use an IF condition.

@KnowEverythin 6 жыл бұрын

I also use if-And function, but program not execute.

@KnowEverythin 6 жыл бұрын

pls, help

@KnowEverythin 6 жыл бұрын

If Application.WorksheetFunction.CountIf(sh.Range("A:A"), Me.TextBox1.Value) > 0 And Application.WorksheetFunction.CountIf(sh.Range("A:B"), Me.ComboBox1.Value) > 0 Then MsgBox "duplicate entery", vbCritical here Textbox1= date and combobox1=shift(A,B,C) nt properly work

@amarsehgal1745 4 жыл бұрын

Hi Sir, how it can be ensured that duplicacy is not there in data entered in the form.

@Exceltrainingvideos 4 жыл бұрын

Which controls in the form are you using?

@amarsehgal1745 4 жыл бұрын

@@Exceltrainingvideos Automatic form and not VB

@Veenu-yi7pc 3 жыл бұрын

This process shows an error 438 and debug how I solve this issue please help

@Exceltrainingvideos 3 жыл бұрын

This link will help: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-worksheet-while-transferring-data-via-userform/

@scarletleaner5636 9 жыл бұрын

Hi Dinesh Sir, Interesting good to know! Everything is excellent Just to add it, Example: If it’s the scenario where we are looking to avoid duplicate entry in multiple fields. Date Item 05-20-2011 Sugar Explanation: If suppose I am entering the same once again by using User form, it should inspect the both column and rows to avoid the same entry ! Please help me on this VBA Code

@javedahmed8343 2 жыл бұрын

Like u there will be few only.

@kidsworld7459 5 жыл бұрын

how to avoid duplicate entries in multiple worksheet while transfering data

@Veenu-yi7pc 3 жыл бұрын

It shows an error 438

@Exceltrainingvideos 3 жыл бұрын

This link will help: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-worksheet-while-transferring-data-via-userform/ You can also download a sample file here.