Is there a way to compare to changing columns in excel and delete duplicates?

Question

So I am trying to implement a barcode scanner in excel. It already enters the barcode as a number in a cell. In another column I have a set of barcodes. So when the scanner provides a barcode, this barcode and one duplicate in the other column shall be deleted.

I have already tried the excel compare-tables function, but neither it deletes the duplicate bar code nor it is able to not mark duplicates in the barcode column. That means: If there is the code 123456 twice in the barcode column it will mark it as a duplicate.

So imagine I have the barcodes 123,123,124,125. Then if I scan 124 I'm expecting the column to contain only 123,123,125, and if I scan 123 I'm expecting it to contain 123,125.

Is there a way to do that in excel or do I need specific software, if so then which to use?

My problem image

Answer 1

You could try import the below code on sheet change event, modify it and try it:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim BarCode As String
    Dim LastRowA As Long, LastRowC As Long, i As Long, Times As Long
    Dim arr As Variant

    'Let us assume that:
    '1. All bar codes appears in column A
    '2. The Selected bar code are in cell B2
    '3. And the results are in column C

    With ThisWorkbook.Worksheets("Sheet1")

        If Not Intersect(Target, .Range("B2")) Is Nothing And Target.Count = 1 Then '<- Check if there is any change in cell B2

            LastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row

            Application.EnableEvents = False

                If LastRowC > 1 Then

                    .Range("C2:C" & LastRowC).Clear '<- If there are data in the results field clear them

                End If

                BarCode = Target.Value '<- Get the code imported

                LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

                arr = .Range("A2:A" & LastRowA) '<- Create an array with all the bar codes

                For i = LBound(arr) To UBound(arr) '<- Loop codes
                    Debug.Print arr(i, 1)
                    If arr(i, 1) = BarCode Then
                        Times = Times + 1
                        If Times > 1 Then
                            LastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row
                            .Range("C" & LastRowC + 1).Value = arr(i, 1)
                        End If
                    Else
                        LastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row
                        .Range("C" & LastRowC + 1).Value = arr(i, 1)
                    End If

            Next i

            Application.EnableEvents = True

        End If

    End With

End Sub

Results: