Duplicate check on export of records

**mbrady1973** · 12-21-2008, 12:42 AM

Good Evening,

I have a spreadsheet that will export records when I "click" a button, to a spreadsheet on a network drive.

I would like to attach some code that will check to see if a duplicate record is going to be written to the network drive, and if so, alert the user that they are about to create a duplicate . If it is a duplicate, give the user the option to overwrite the existing record.

Ex. I export the results for account 12345.....Account 12345 is now written to an outside sheet.

User2 logs in and works with customer 12345, w/out knowing that someone has already worked with this account, presses the export button.....This is the trigger point for what I want to happen. At this point when it locates a dupe in the outside spreadsheet it will prompt the user too make some chouces .

I hope this makes sense..

Please find attached the code I would like to attach this to...

HTML Code:

**rylo** · 12-21-2008, 11:48 PM

Hi

There are a couple of ways you could do this.

1) Use the worksheetfunction.countif on the column that contains the ID and if it is > 0 then you have a duplicate.

Please Login or Register  to view this content.

2) Use find to set a range variable to that ID. If it is nothing then it doesn't exist and has to be added. If not then you get a reference point for the update. In your case, this will probably be the better / easier option.

Please Login or Register  to view this content.

You haven't given any indication of where the data is coming from on the input workbook, or where to look on the output so giving you more than generic code structure is a bit difficult.

HTH

rylo

**mbrady1973** · 12-27-2008, 11:05 PM

Hi,

Thanks for answering....

I am very new to VBA.

I have a workbook called 'data transfer automation'. I have a button that when pressed, exports info to a workbook called 'format for PI assistant'.

When that button gets pressed, I would like the code to look in the export workbook (format for PI assistant), column 'account number' to see if the account number is in fact a dupe..??

If it is I would like it to warn, and offer the opportunity to overwrite the existing record, or not...

Just not sure how to do this.

Thanks!

**rylo** · 12-28-2008, 01:04 AM

Hi

Might be easier for you to put up example copies of both the workbooks, with some sample data. Make sure your formats, sheet names, positions are the same as the real items, but don't have any sensitive data. Include the buttons and the associated code....

rylo

**mbrady1973** · 12-28-2008, 01:36 PM

Thanks,

Please find attached a copy of bot hthe main and the export wb.

What I would like to have happen, is that upon pressing the "submit Results" button, that it would check to see if the account number had already been written to the "format for PI......" workbook, and if so, let the user choose to do nothing, overwrite.

Again, thanks.....I wish I understood this better!

**rylo** · 12-28-2008, 04:32 PM

Hi

Can you please either provide the password or unprotect the datafrforforum.xls workbook and sheets.

rylo

**mbrady1973** · 12-28-2008, 04:48 PM

GrayMck1425 will unprotect

thanks

**rylo** · 12-28-2008, 06:00 PM

Hi

Hopefully this will point you in the right direction.

I've also changed a couple of your variables. It isn't a good idea to use reserved words as user defined variables. I can lead to confusion for both the application and anyone else that is trying to interpret the program.

rylo

Please Login or Register  to view this content.

**mbrady1973** · 12-28-2008, 06:55 PM

Thank you for your time,

will i need to do anything with this code?

this stuff really confuses me.

thanks again.

**rylo** · 12-28-2008, 07:40 PM

Hi

I'd rename the existing code (change the name to Transferdataold) and then put in the new code. Run as per normal and see how it goes.

The other thing I'd do is remove the duplicates in format for PIASSIST.xls so you have the right number of entries, and make sure that the 2 workbooks align for the customer numbers

rylo