How To Prevent Duplicate URL Entries in the Cell in a Column?

[kalucharan]

Dear Experts,

I tried to solve above problem using Data Validation formula but it does not help as per my wish. Because data validation does not work if somebody copy paste the duplicate values,it works when somebody types duplicate values in next column. So if I want to enter lot of urls to a column then it is completely impossible to type so must have to copy paste from the browser. I have created a sample sheet and kindly requested you all to solve this problem either using formula or using Data validation in sample 1 and sample 2 respectively.

Using Any Formula to do this
[TABLE="width: 500"]
<tbody>[TR]
[TD]URL[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD][TABLE="width: 291"]
<tbody>[TR]
[TD="width: 291"]https://www.google.com/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 291"]
<tbody>[TR]
[TD="width: 291"]https://www.facebook.com/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]http://www.mrexcel.com/forum/search.php?searchid=970927[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]http://www.mrexcel.com/forum/search.php?searchid=970927[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]https://www.google.com/[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Using data validation if it is possible
[TABLE="width: 500"]
<tbody>[TR]
[TD]https://www.google.com/[/TD]
[/TR]
[TR]
[TD]https://www.facebook.com/[/TD]
[/TR]
[TR]
[TD]http://www.mrexcel.com/forum/search.php?searchid=970927[/TD]
[/TR]
[TR]
[TD]http://www.mrexcel.com/forum/search.php?searchid=970927[/TD]
[/TR]
[TR]
[TD]https://www.google.com/[/TD]
[/TR]
</tbody>[/TABLE]

Thanking you for your help in advance.

Regards

 

[Peter_SSs]

Welcome to the MrExcel board!

To do that, you would need to use vba code. However, the user would have to have macros enabled. If macros are not enabled, then the vba method is also defeated.

So there isn't a perfect answer to this but post back if you want a suggestion for the macro approach.

 

[kalucharan]

Welcome to the MrExcel board!

To do that, you would need to use vba code. However, the user would have to have macros enabled. If macros are not enabled, then the vba method is also defeated.

So there isn't a perfect answer to this but post back if you want a suggestion for the macro approach.

Thank you for the reply.
Could you please suggest if you can do it using macro.

 

[ukmikeb]

Cross-posted - How To Prevent Duplicate URL Entries in the Cell in a Column?

 

[Peter_SSs]

Test in a copy of your workbook

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled file (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sDel As String
  
  Const ColOfInterest As String = "A" '<-Column where you want to prevent duplicates
  
  Set Changed = Intersect(Columns(ColOfInterest), Target)
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If WorksheetFunction.CountIf(Columns(ColOfInterest), c.Value) > 1 Then
        sDel = sDel & vbLf & c.Value
        c.ClearContents
      End If
    Next c
    Application.EnableEvents = True
    If Len(sDel) Then
      MsgBox "Duplicate values entered have been removed:" & sDel
    End If
  End If
End Sub