Preventing Duplicates Across many ranges

[sdkorin]

Greeting all!

I am working with a spreadsheet that tracks staffing across multiple areas. What I need is to be able to prevent duplicates in specific areas of the spreadsheet. It has to be these specific areas because some areas are supposed to allow duplicates. Here are my ranges:

=$C$1,$M$1,$Z$51,$AC$51,$B$32:$B$51,$D$32:$D$51,$F$32:$F$51,$H$32:$H$51,$N$34:$N$51,$R$34:$R$51,$P$34:$P$45,$B$5:$B$28,$D$5:$D$28,$F$5:$F$28,$H$5:$H$28,$J$5:$J$28,$L$5:$L$28,$N$5:$N$28,$P$5:$P$28,$R$5:$R$28,$J$34:$J$51,$AC$28:$AD$48,$W$30,$T$32,$T$34,$AC$27,$U$5:$U$24,$X$5:$X$24,$AA$5:$AA$24,$AD$5:$AD$24,$P$48:$P$51,$L$34:$L$51,$BK$3:$BL$31,$W$34,$W$32,$G$1,$Q$1,$AG$22:$AH$31,$AJ$22:$AK$31,$AM$22:$AN$31,$AP$22:$AQ$31,$AS$22:$AT$31,$AY$3:$AZ$20,$BB$3:$BC$20,$BE$3:$BF$20,$BB$34:$BC$51,$BE$34:$BF$51,$BB$23:$BC$31

No individuals should appear twice in the spreadsheet within those ranges.


I really appreciate the assistance.

 

[Peter_SSs]

Are you trying to do this with standard Data Validation or vba?
Both methods have drawbacks ..
- DV is defeated if the user pastes from an area that doesn't have the same DV.
- vba is defeated if the user has not enabled macros.

 

[sdkorin]

Are you trying to do this with standard Data Validation or vba?
Both methods have drawbacks ..
- DV is defeated if the user pastes from an area that doesn't have the same DV.
- vba is defeated if the user has not enabled macros.

I think VBA would work the best. Copy/Paste is something that occurs a lot in this spreadsheet, so DV won't really work.

 

[Peter_SSs]

I think VBA would work the best.

In that case, in a copy of your workbook, you could try the following.

1. Select all those cells/ranges in question and make that a named range. I have used the name 'NonDupeRange'
2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 1.
4. Close the Visual Basic window & test.
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Static d As Object
  Dim Changed As Range, c As Range
  Dim sDupes As String
  
  Set Changed = Intersect(Target, Range("NonDupeRange"))
  If Not Changed Is Nothing Then
    If d Is Nothing Then
      Set d = CreateObject("Scripting.Dictionary")
      d.compareMode = 1
    End If
    d.RemoveAll
    For Each c In Range("NonDupeRange")
      If Not IsEmpty(c.Value) Then d(c.Value) = d(c.Value) + 1
    Next c
    For Each c In Changed
      If d(c.Value) > 1 Then sDupes = sDupes & "," & c.Address(0, 0)
    Next c
    If Len(sDupes) > 0 Then
      Range(Mid(sDupes, 2)).ClearContents
      Range(Mid(sDupes, 2)).Select
      MsgBox "The following cell(s) you entered contained duplicates and have been cleared." & vbLf & Mid(sDupes, 2)
    End If
  End If
End Sub

 

[sdkorin]

In that case, in a copy of your workbook, you could try the following.

1. Select all those cells/ranges in question and make that a named range. I have used the name 'NonDupeRange'
2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 1.
4. Close the Visual Basic window & test.
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Static d As Object
  Dim Changed As Range, c As Range
  Dim sDupes As String
  
  Set Changed = Intersect(Target, Range("NonDupeRange"))
  If Not Changed Is Nothing Then
    If d Is Nothing Then
      Set d = CreateObject("Scripting.Dictionary")
      d.compareMode = 1
    End If
    d.RemoveAll
    For Each c In Range("NonDupeRange")
      If Not IsEmpty(c.Value) Then d(c.Value) = d(c.Value) + 1
    Next c
    For Each c In Changed
      If d(c.Value) > 1 Then sDupes = sDupes & "," & c.Address(0, 0)
    Next c
    If Len(sDupes) > 0 Then
      Range(Mid(sDupes, 2)).ClearContents
      Range(Mid(sDupes, 2)).Select
      MsgBox "The following cell(s) you entered contained duplicates and have been cleared." & vbLf & Mid(sDupes, 2)
    End If
  End If
End Sub

I thought it was gonna work at first, but it turns out it won't work with merged cells, for which I have many in the applicable areas. Any work arounds?

 

[Peter_SSs]

In general, merged cells and vba do not go well together.
However, to have any chance of alternate considerations, details of merged/not merged cells would be required.