How to Find Duplicate Entry from Two Files

[jithu_ex]

Hello Guys,

I have two Files for Invoice Entry. "Invoice Entry Master" and "Invoice Entry Daily". I am pasting the data from "Invoice Entry Daily" to "Invoice Entry Master" on a regular basis. I would like to know whether there is any VBA code for checking if the Invoice Number is already exist in the "Invoice Entry Master" file while I am entering the Invoice Entries in to my "Invoice Entry Daily File". I want to just pop up 1 message box.

My files are located in this path "C:\Users\Admin\Invoice Entry Master.xlsx" and "C:\Users\Admin\Invoice Entry Daily.xlsx".

As per my knowledge I have to use a loop and check in my "C:\Users\Admin\Invoice Entry Master.xlsx" if the Invoice Number I have entered is already exist in my "Invoice Entry Master" file.

Please let me know

Thanks in Advance

[xladept]

Hi Jithu,

You can load a dictionary with the master entries and then just see whether what you've typed already exists!

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Trebor76]

Hi jithu_ex,

Welcome to the forum!!

You could just have a VLOOKUP formula in your "Invoice Entry Daily File" file (ideally in the column next to where you're entering the invoice numbers) that looks up your entry to the "Invoice Entry Master" workbook and if found returns a message saying that invoice number has been used or no message if it hasn't been used. No need for VBA.

HTH

Robert

[jithu_ex]

Million Thanks for your reply.

In my Excel master file the Invoice numbers are in column D and in my Daily Entry file also the Invoice Number is in Column D.

Could you please tell me how would be the Vlookup formula to find whether the Invoice Number is duplicate? My apologies I am very new to the advanced functions of Excel.

Thanks

[Trebor76]

What is the sheet name in the "Invoice Entry Master" workbook that contain the invoice numbers?

[jithu_ex]

Sheet1 and in the Daily Entry file also the name of the sheet is Sheet1

[jithu_ex]

Hi,

Any updates?

Thanks

[Trebor76]

Put this formula in the first cell (ideally cell E2) you wish to check the invoices from in the "Invoice Entry Daily" file...

=IF(ISERROR(VLOOKUP(D2,'C:\Users\Admin\[Invoice Entry Master.xlsx]Sheet1'!$D:$D,1,FALSE)),"","Invoice number " & D2 & " has already been used.")

...and copy it as far down the column you need.

Regards,

Robert

[jithu_ex]

Thanks a lot

[Trebor76]

You're welcome. If you could mark the thread as solved it would be appreciated.