Prevent Duplicate Entries in Excel Worksheet While Transferring Data from UserForm

Hello,
I'm begging for help from the experts and those who are well versed in vba. Thank you

The Exercise
What I am trying to achieve is to use a simple userform to record a data on an excel worksheets.
Worksheet has 5 columns in total Supplier (A), Service (B), Reporting Month (C), Sales (D) and Commission (E)
A is just a text (name)
B is also a text (name)
C in a date on format (mm-yy)
D and E are numbers (currency)

The Trick
I would like the vba to prompt me if values already exist based on values from 2 different columns from he same row.

Solution so Far: 1 column
I have found the way to warn me if let's say one of the column is unique ID which is not the case in my example so therefore I have to check two columns in order to assess whether there is a duplication or not. That is, column A (supplier) and column C (reporting month)
So as an example if Tesco has already reported for Aug-18 then this will flag up as a duplicate.
Now, if either column A or column C is a unique value then as soon as the same value is entered then it will flag up, as per the following vba code (for supplier as a unique ID)

Please Login or Register  to view this content.

The Issue
I would like to have a code that would do similar thing but extended to 2 columns criteria - that is, column A and column C to be check for duplication.

Any help? Thank you and much love.

I used countif but it doesn't look like it did the job.
The issue is that I need to look whether the value entered in ComboBox 3 and ComboBox 9 are not already there.

Please Login or Register  to view this content.

Try change to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hello,
Thank you daboho for your input. Much appreciated. Unsure whether it is the best function to do it but it did work. However, I only get the message box for certain duplicates and not existing ones, although there are definitely duplicate since I did test the vba first before roll it out.

What could be the issue for the vba not to pick up all existing duplicates?

Much appreciated.

Please Login or Register  to view this content.

Thanks Dahoho. I may be doing the incorrect things here because every entry now appears to a duplicate and the message box shows all the time.

Also, I was thinking would it be possible to use MATCH on array formula like this one on vba to find the duplicate?

Formula:

Please Login or Register  to view this content.

This is not obviously a true excel formula but let's assume ComboBox is the cell C, G_Supplier is the suppliers column range and O_Reportingmonth is the reporting month >

Formula:

Please Login or Register  to view this content.

The below rendition however doesn't seem to do the work.

Please Login or Register  to view this content.

Still on the learning curve
Perhaps an evaluate function with match will help here. Anyone?

What am I missing here, please? Cannot figure it out. Much appreciated.

Please Login or Register  to view this content.

Hello,

I got a run-time error 424 on the second filter on the z.

Please Login or Register  to view this content.

Anyone could help on this one please? Thank you.

Hello @Daboho I was using the last coding you gave but it got run-time 424 error. I'm sure the tweak is very simple to get it right but alas I couldn't find it.

Daboho,

Thank you for your contribution but it doesn't look like it's doing the job I intended to do. I got loads of error using the last code you provided.

I think, I'm tempted to stay with the below one but somehow I'm missing something to make it work properly.

Please Login or Register  to view this content.

Thanks Nigelog

Do you think the code below sounds correct to you? (this is part of the code I published earlier)

Please Login or Register  to view this content.

I got a mismatch error on the code on the reply above.

attach a sample workbook to make it easier for people to view. Go Advanced>Manage Attachments. Would take too much time to recreate your userform and sheet layout

Hello,
This is a slim down version of my workbook.
My request is that, once I enter a value in ComboBox 3 (Suppliers) and ComboBox 9 (Reporting Month) of the userform, when I hit the Record button, then it will check whether the combination of Supplier in Column G and Reporting Month in Column O is already there so therefore it will come back as a duplicate if the data exist otherwise I will copy all values in the userform to a new row on the Sales worksheet.
Thank you

Attached is a working model of a userform using combobox3 and 9 as search criteria

It is self explanatory and may help you to understand the steps taken to achieve the result

This is a filtering option that counts the remaining rows

Please Login or Register  to view this content.

Thank you very much both of you for your contribution.
I have yet to test nigelog last code.
I have updated the slim down version of my workbook so that the userform can work properly. ComboBox 3 and 9 are now functional. And it corresponds to column G and O respectively of the Sales worksheet.
Unsure it will help better understand my issue but here it is.
Once again thank you.

Al
the answer is in the workbook I attached. Adapt it for your userform, I had no time to sort yours out with list entries etc. That code is set for your data as you posted so will work out of the box once you add it to your new_frm command button1. You can build up from there but that is the first hurdle covered.

Thanks nigelog. I'll check and test it in the morning and I have no doubt t will work. Your contribution as well as daboho's are much appreciated.

Thanks nigelog. I like the message box that counts how many duplicate is there.
Just a quick question, when it says "One Record" on the message box when it is executed, does it mean, the values in the userform is the only record and not an existing value on the Sales spreadsheet, so no duplicate and thus asking me to carry on with the Macro? So therefore, any value on the message box above 2 will mean duplicate and 1 would mean first record? Am I right to understand it this way?
Thanks

Yep you got it

Just 1 record is the header row being counted as visible....change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

which will return "0 records"

I think this has done the job nigelog. It looks like it's working perfectly as intended.
Quick question. How to undo the .Autofilter to display the whole table/list on the Sales sheet afterward?

At the end of the sub

Please Login or Register  to view this content.

removes the autofilter, should have been in the code

If that answers the question can you kindly mark as solved, thanks for the feedback

I've omitted the add .Autofilter after else

It's the small things lol, glad to help and thanks for the feedback