Solved

Excel Workbook - Prevent Value Duplicates in one column all worksheets of a workbook

Posted on 2016-07-18
4
17 Views
Last Modified: 2016-07-18
I have a excel workbook with many rows of data.  There are also many worksheets - each worksheet relates to a week of data.  In column E of all worksheets combined there cannot be duplicates.  So I want the workbook to prevent me from entering a duplicate value.  A prompt saying "Duplicate Found" or the cell going red would be good.

I'd imagine this would be done on a change event with VB of a Macro Enabled Workbook
0
Comment
Question by:iits
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41718062
do you have a sample can upload here so we can try to diagnose the issue?
0
 

Author Comment

by:iits
ID: 41718066
0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41718097
just a quick test...

in each of the worksheet, try add:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call checkDuplicate(Target)
End Sub

Open in new window

then create a public module and add:
Public Sub checkDuplicate(v As Range)
    isDup = False
    
    If v.Cells.Count > 1 Then Exit Sub
    
    If v.Value = "" Then
        isDup = False
        Exit Sub
    End If
    
    For Each w In Worksheets
        Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            If (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column) Then
                Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        After:=v, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            End If
            isDup = Not (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column)
            If isDup Then
                MsgBox "Duplicate value found at worksheet: [" & w.Name & "], cell (" & Rng.Row & ", " & Rng.Column & ")", vbCritical, "Error"
                Exit Sub
            End If
        Else
            isDup = False
        End If
    Next
End Sub

Open in new window

UniqueExample_b.xlsm
0
 

Author Closing Comment

by:iits
ID: 41718102
Billiant! Thank You
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question