Solved

Excel Workbook Reference

Posted on 2012-04-13
10
306 Views
Last Modified: 2012-04-16
Hello-

I need to scan some barcodes.

I need a list of total barcodes in sheet1, and then when I scan them into sheet2, I need to ensure there are no duplicates in sheet 2(if there are a msg box would be great).  I am assuming I would need coding for this, can anyone help out?

I already have the bardcodes to "match" in sheet 1, I just need a reference in sheet 2 saying "look in sheet 1, make sure there is a match, then look in sheet 2 and make sure this number doesnt appear anywhere"

Thanks!
0
Comment
Question by:Frankkadaf
  • 5
  • 4
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 37845533
Do you want to catch the duplicates after the fact and delete them or do you want to actively prevent duplicates?

Are familiar with the Match() worksheet function?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37845553
How about a formula?  If your scan codes in Sheet 1 are in column A, and in Sheet 2 you are also scanning them in column A, then we just need a test for uniqueness of all the numbers being scanned in Sheet 2.

If you need indicators of which scan code might be a duplicate, you can put this in Sheet 2, cell B2 and copy down (assumes scan codes start in row 2 on both sheets and go to 999, and you can change that range):

[B2]=COUNTIF(Sheet1!$A$2:$A999,$A2)

or

[B2]=COUNTIF(Sheet1!$A:$A,$A2)

and copy down.  This assumes that the header in row 1 is different between the two sheets - e.g., "Scan Codes in System" header in A1 of Sheet1, and "Scan Codes being Scanned" in A1 of Sheet2.

If you just want to be advised that you have a duplicate (you can check one formula as you scan and if it advises you have a duplicate, you can address it right then):

Then, in D2, for example, you can sum what's in column B to identify how many duplicates were find (hopefully, that number doesn't go over 1 as you're addressing the duplicates as you scan:

Simply by using
[D2]=SUM(A:A)

However, if you only want ONE indicator for duplicates, you can forget about what I said, above, and use this formula:

[D2]=SUMPRODUCT(COUNTIF(Sheet1!$A:$A,$A:$A))

You can even add conditional formatting - e.g., on this formula, enhancing the formula with:

[D2]=IF(SUMPRODUCT(COUNTIF(Sheet1!$A$1:$A$1000,$A$1:$A$1000))>0,"YOU HAVE DUPLICATES","NO DUPLICATES")

And you can use conditional formatting on D2 to highlight RED when there are duplicates with:

=$D$2="YOU HAVE DUPLICATES" and set the fill to RED

See attached where column A in both sheets has a random number generator so you get different numbers with each calculation.  Of course, you'd populate Sheet 1 with your current numbers, then start populating sheet 2 as you scan.

Cheers,

Dave
showDuplicates-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37845569
For a VBA approach, put this code in the Sheet2 codepage (hit ALT-11 and look to the left for VBPROJECT(showDuplicates r2.xls), then double click on Sheet 2 to select it.)

Then, copy/paste this code into Sheet2's codepage:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksSheet1 As Worksheet
Dim rSheet1 As Range
Dim rSheet2 As Range
Dim vDuplicates As Variant
Dim xMsg As Long
Dim strDups As String
Dim r As Range

    Set wksSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set rSheet1 = wksSheet1.Range("A1", wksSheet1.Range("A" & wksSheet1.Rows.Count).End(xlUp))
    Set rSheet2 = Range("A1", Range("A" & Rows.Count).End(xlUp))

    vDuplicates = Evaluate("SUMPRODUCT(COUNTIF(Sheet1!$A$1:$A$1000,$A$1:$A$1000))")
    If Not IsError(vDuplicates) And vDuplicates > 0 Then
        xMsg = MsgBox("Duplicate found, please address this issue" & vbCrLf & vbCrLf & "Do you want a list of locations with duplicates?", vbYesNo, "Hit yes to get the list")
        If xMsg = vbNo Then Exit Sub
        For Each r In rSheet2
            vDuplicates = Evaluate("=COUNTIF(Sheet1!$A$2:$A$1000," & r.Address & ")")
            If Not IsError(vDuplicates) And vDuplicates > 0 Then
                If strDups = vbNullString Then
                    strDups = "ScanCode: " & r.Value & " At " & r.Address
                Else
                    strDups = strDups & vbCrLf & "ScanCode: " & r.Value & " At " & r.Address
                End If
            End If
        Next r
        MsgBox strDups
    End If
End Sub

Open in new window


PS - the ALARM popup on duplicates occurs when you make a sheet change, so hitting F9 for the random numbers won't do it, but making a value change will.  Let me know if it works with your scanner, as I'd need to make a modification to handle if its not tripping the Worksheet_Change() event automatically.

So if r2 doesn't work for you, the use r3, which acts on the Worksheet_Calculate() event and should update and trigger the ALARM on duplicates when the scan codes are entered.

See attached.

Cheers,

Dave
showDuplicates-r2.xls
showDuplicates-r3.xls
0
 

Author Comment

by:Frankkadaf
ID: 37848602
I would like to actively catch them.

Looking at the VBA solution, it stops a duplicate from sheet 1 to sheet 2.  I would like to make sure the number appears in sheet 1 (from a bank of pre-entered barcodes) and only appears in sheet 2 one time.

So it has to match sheet 1, and then if it does, it can be entered, unless the same number (or in this case barcode) has already been entered.

At this point, I have a countif in column 2 =COUNTIF('Reference Sheet'!$B$1:$B$788,A1)>0

So that if it appears in Sheet 1, it comes up true, then I have VBA code in Sheet 2:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Cells.EntireColumn
 
    If Application.WorksheetFunction.CountIf(rng, Target.Cells.Value) > 1 And Target.Cells.Value <> "" Then
        MsgBox "Duplicate Value"
        Application.EnableEvents = False
        Target.Cells.Value = ""
        Application.EnableEvents = True
    End If
 
 
End Sub

I was hoping to get this all in 1 VBA , or if I can use Msg Box with countif, that would work too.  Any thoughts?  Thanks!
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37849400
Ok - first a match is used to ensure what's being entered in Sheet2 matches sheet 1, otherwise an error message pops up, and the entry is reversed via Application.Undo.  If it makes it past that first screen, and the item does exist in Sheet1, then it checks to see that only one instance of that item being entered exists in Sheet2, otherwise an error message pops up in that event and the entry is reversed via Application.Undo.

Here's the code which goes in Sheet 2's codepage:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksSheet1 As Worksheet
Dim rSheet1 As Range
Dim rSheet2 As Range
Dim vDuplicates As Variant
Dim vMatch As Variant
Dim xMsg As Long
Dim strDups As String
Dim r As Range

    If Target.Count = 1 And Target.Column = 1 And Target.Value <> vbNullString Then  'scanning should bring 1 code at a time, if this is copy/paste, do no validation; Scan codes are column 1, do no validation in other columns
    
        Set wksSheet1 = ThisWorkbook.Sheets("Sheet1")
        Set rSheet1 = wksSheet1.Range("A1", wksSheet1.Range("A" & wksSheet1.Rows.Count).End(xlUp))
        Set rSheet2 = Range("A1", Range("A" & Rows.Count).End(xlUp))
    
        'look for match of what was entered, with column A in Sheet 1
        vMatch = Application.Match(Target.Value, rSheet1, 0)
        If IsError(vMatch) Then
            MsgBox "Scan code in Sheet2 does not have a match in Sheet1", vbCritical, "Undoing last scan"
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        Else
            'Make sure scan code being entered in Sheet 2 has no duplicates in Sheet 2
            vDuplicates = Application.CountIf(rSheet2, Target.Value)
            If Not IsError(vDuplicates) And vDuplicates > 1 Then
                MsgBox "Valid Scan Code: " & Target.Value & " already entered in Sheet 2.  Duplicate found in Sheet 2, please address this issue", vbCritical, "Undoing last scan"
                Application.EnableEvents = False
                Application.Undo
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Open in new window


See attached.

Dave
showDuplicates-r4.xls
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Frankkadaf
ID: 37851421
That works perfect, how can I change the sheet names?  What changes do I need to make to the code?  Anywhere it says Sheet1?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37852226
See line 25
0
 

Author Closing Comment

by:Frankkadaf
ID: 37853266
Great solution, thanks!
0
 

Author Comment

by:Frankkadaf
ID: 37853271
So should I change the application.enableevents to false?

It appears that code is the same?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37854159
The rest of the code shouldn't change

Having problems?
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

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

14 Experts available now in Live!

Get 1:1 Help Now