Incrementing occurrences while keeping first instance of barcode

[hasan mougharbel]

Hello everyone, I hope you're doing well. I have a question regarding our simple barcode tracking system in Excel.

I have a list of barcodes in column A that need to be entered manually, and column B would track the number of occurrences of all barcodes entered in column A.
When a barcode is first entered in column A, I want to register it along with the number "1" in column B. However, if the same barcode is re-entered in column A, I want to prevent Excel from writing it again in column A. Instead, I want to keep the first instance of barcode in column A and to increment its corresponding cell in column B by 1 for every subsequent occurrence of the same barcode.
For example, based on the excel sheet provided, when "11112" appears for the first time in column A, it should be registered in column A along with the number "1" in column B. If "11112" appears again in a different row in column A, Excel should restrict writing this barcode again in column A and only to add "2" next to "11112" in the first row. The row where the barcode is re-entered should be left blank automatically.

How can I achieve this using Excel formulas or VBA code?

Thanks for taking care of my enquiry..

[AliGW]

It cannot be done with formulae: if you prevented the bar code entry using data validation, then there would be no way of a formula knowing that anyone had tried to reenter it. It will have to be VBA.

[JohnTopley]

Please Login or Register  to view this content.

Code in Sheet1 (Right click on tab>>view code)

[hasan mougharbel]

The current implementation generates blank cells as intended, but when I hit Enter, the active cell moves to the next one.
What I'm aiming for is to have the blank cell stay in place after pressing Enter. This would significantly streamline my workflow.
Thanks for taking care of this..

[bebo021999]

Try this:

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim f, rng As Range 
If Intersect(Target, Range("A3:A100")) Is Nothing Or Target.Count > 1 Then Exit Sub 
Set rng = Range("A2", Target.Offset(-1)) 
Set f = rng.Find(Target) 
Application.EnableEvents = False 
If Not f Is Nothing Then 
    f.Offset(0, 1).Value = f.Offset(0, 1).Value + 1 
    Target.Value = "" 
    Target.Select 
Else 
    Target.Offset(0, 1).Value = 1 
End If 
Application.EnableEvents = True 
End Sub  

[hasan mougharbel]

Hey dear "bebo021999" ...this solved my case, but when a new barcode is entered, the next active cell, shall move downward by one cell, and not to move to the right, as in your case..Thanks a lot for taking care of this..

[JohnTopley]

Please Login or Register  to view this content.

[bebo021999]

OK. Try again.

..................... 
Else 
    Target.Offset(0, 1).Value = 1 
    Target.Offset(1, 0).Select 
End if 
....................  

[hasan mougharbel]

I want to bring up an important matter related to John and Bebo's vba that have been working quite well so far. However, there seems to be an oversight thats pending a correction.
Currently, their vbas allows for partial barcode matches to be considered valid, which is causing some false positives. For instance, if we enter "7777" or even just "7," it is incorrectly considered to be "7777-01." This is not the behavior we desire, as it may lead to inaccurate results. I appreciate a lot your assistiance to finalize this at your sole convenience.

[AliGW]

Whilst I understand that you may be in a bit of a panic, don't tell us that it is urgent, please. All your helpers here are volunteers giving freely of their own time: you haven't paid a penny for any assistance you get here, therefore expecting any form of speed response or preferential treatment, or seeming to, is inappropriate.

Thank you for your understanding. I have removed the urgency notice from your last post.

Moderator

[JohnTopley]

Currently, their vbas allows for partial barcode matches to be considered valid, which is causing some false positives. For instance, if we enter "7777" or even just "7," it is incorrectly considered to be "7777-01."

Not on my testing!!

I entered "7777-01" and count of 1. Then entered 7777 and count of 1 (new entry)