Here's a function that may help you do just that. Read the full article to help you call the function:
prevent-data-duplication-in-excel-vba
Function detect_duplicates(ByVal incomingData, dataToMatch, dataMatchRatio)
'Loop first through new data array
For a = 1 To UBound(incomingData)
'Nested loop through comparison data array
For b = a To UBound(dataToMatch)
'Compare values between arrays
If incomingData(a) = dataToMatch(b) Then
'If values are the same then increase duplicateRatio
duplicateRatio = duplicateRatio + 1
End If
Next b
Next a
'Case the actual percentage of similarity
Select Case (duplicateRatio / UBound(dataToMatch))
'If the percentage exceeds threshold value
Case Is >= dataMatchRatio
'Prompt user to identify threshold and to alert that threshold is exceeded
MsgBox "A " & (dataMatchRatio * 100) & "% match or over has been found! You may be duplicating data. Aborting..."
'Data set is a duplicate
detect_duplicates = 1
Case Is < dataMatchRatio
'Data set is not a duplicate
detect_duplicates = 0
End Select
End Function