Solved

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

Posted on 2016-07-18
4
11 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
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

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

Author Comment

by:iits
Comment Utility
0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 500 total points
Comment Utility
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
Comment Utility
Billiant! Thank You
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now