Prevent duplicate values in workbook

[KevinMMO]

I have a table in excel in which data like serial number and date are registered and after that, a button is pressed to send them to another tab to make a database. What I would like to do is to prevent the user of entering a duplicated value.
The "Concatenate" column was made because the "part #" is changed, so there can be serial numbers repeated, but of different part #. What I want to do is to make a macro that checks in the "Database" sheet (column 3 is where concatenate values are stored) if the value is already there, and if it is repeated, send a message to the user and not allowing him to insert that value.

Note:Concatenate values are made with formula =concatenate(serial number,part #), so I can't apply data validation since the user is not typing the cell value and its a calculated formula.

#	Serial Number	Date	Part #	Description	Employee	Concatenate
1	10	may 22	1234			101234
2	11	may 22	1234			111234
3	12	may 22	1234			121234
4	13	may 22	1234			131234
5	14	may 22	1234			141234
6	15	may 22	1234			151234
7	16	may 22	1234			161234
8	17	may 22	1234			171234
9	18	may 22	1234			181234
10	19	may 22	1234			191234
11	20	may 22	1234			201234
12	21	may 22	1234			211234

<tbody>
</tbody>

 

[JLGWhiz]

How does the user transfer the data to the database sheet? eg. Copy/Paste, macro, manually, single line item, multiple rows, etc.

 

[iliace]

This can get complicated. I tried setting up an event-driven code, but quickly ran into several complications.

How does the Database sheet get updated? Suppose a new value is entered. More interestingly, suppose a row is modified (or deleted) - what happens to the database file?

 

[KevinMMO]

From a macro that copies that range and sends it to the sheet "database", then clears the first column.

 

[iliace]

In my example that I built - on top of your data, in row 13, I enter:

Serial number: 22
Part number: 1234

Then, in row 14, I enter:

Serial number: 20
Part number: 1234

My macro kicks it out (because it already exists in row 12), saying there is a duplication, and clears the entry.

Then, in row 14, I enter the same thing as in my first step (row 13):

Serial number: 22
Part number: 1234

Nothing happens, even though this is a duplicate. That is because my first entry was not registered in Database sheet column C. My duplicate entry is allowed.

To prevent this from happening, I would have had to register the first entry I made (221234, row 13) in the Database immediately after it happened.

So then, here is the question: how does the Database sheet get updated? Should this update be automatic upon entry of a new item? If so, how are the other columns in Database sheet populated?

 

[KevinMMO]

For example, after filling the serial numbers from the 12 rows, the rest Isabel calculated. Then you press a button with the macro that copies that data and pastes in a data base. After that, the fields área cleared with a macro. The next time you fill the fields and press the button, they will go to the last row with data of the database.

 

[iliace]

the rest Isabel calculated.

Who/what is Isabel?

Then you press a button with the macro that copies that data and pastes in a data base.

This is my question above that you did not answer. What happens if there is data duplication before commit? The duplicate values are already entered, but not yet in the database.

 

[JLGWhiz]

For example, after filling the serial numbers from the 12 rows, the rest Isabel calculated. Then you press a button with the macro that copies that data and pastes in a data base. After that, the fields área cleared with a macro. The next time you fill the fields and press the button, they will go to the last row with data of the database.

If you post the code you use to update the database, maybe it can be modified to do what you want to avoid duplicates.

 

[KevinMMO]

Public Sub copiar() 'macro terminado para copiar datos e insertarlos en base de datos


'Declare Variables
Dim wsOrigin As Worksheet
Dim wsDataBase As Worksheet
Set wsOrigin = ThisWorkbook.Sheets("HojadeInspection")
Set wsDataBase = ThisWorkbook.Sheets("Base de datos")

Application.ScreenUpdating = False

'Copy/Special Paste Desired Data
Dim COPYME As Range
'Dim RCount As Integer
Dim RCount2 As Integer

'RCount = wsOrigin.Range("L" & wsOrigin.Rows.Count).End(xlUp).Row
RCount2 = wsDataBase.Range("A" & wsDataBase.Rows.Count).End(xlUp).Row

Set COPYME = wsOrigin.Range("B9:H20")
COPYME.Copy

wsDataBase.Range("A" & RCount2 + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

If I understand you correctly, column C in database sheet amd column F in the source sheet hold concatenated values. If that is true, then this should prevent duplicates from being copied over.

Public Sub copiar() 'macro terminado para copiar datos e insertarlos en base de datos
 'Declare Variables
 Dim wsOrigin As Worksheet, c As Range
 Dim wsDataBase As Worksheet
 Set wsOrigin = ThisWorkbook.Sheets("HojadeInspection")
 Set wsDataBase = ThisWorkbook.Sheets("Base de datos")
 Application.ScreenUpdating = False
 'Copy/Special Paste Desired Data
 'Dim COPYME As Range
 'Dim RCount As Integer
 'Dim RCount2 As Integer
 'RCount = wsOrigin.Range("L" & wsOrigin.Rows.Count).End(xlUp).Row
 'RCount2 = wsDataBase.Range("A" & wsDataBase.Rows.Count).End(xlUp).Row
 For Each c In wsOrigin.Range("F9:F20")
    If Application.CountIf(wsDataBase.Range("C:C"), c.Value) = 0 Then 'Check for duplicate
       wsOrigin.Cells(c.Row, 2).Resize(, 7).Copy 'If no dupes
        ws.DataBase.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues 'copy to next available row ind db sheet
    End If
 Next
Application.CutCopyMode = False
 'Set COPYME = wsOrigin.Range("B9:H20")
 'COPYME.Copy
 'wsDataBase.Range("A" & RCount2 + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.ScreenUpdating = True
 End Sub