Unique identifierClosed

Question

Good day!
I would appreciate if anyone could help me out!
I'm a small reseller! I started out with a few items now i have almost one thousand! Is there a formula that i could use to make unique identifiers for each one of those items and eventually male that identifier into a barcode! 
I was doing it manually
Example
I have an item from adidas, i was creating AD1000 and so on!
Is there an easier way to do this?
Thank you so much!

vcoolio · Answer

Hello JC0389,
A VBA code may help you as your list grows. Try the following code in a standard module:-
Sub CreateSerialNos()

Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

With Range("B2:B" & lr)
        .Formula = "=Left(A2,2)&Row()+1000"
    End With
    
UpperCase

End Sub

Sub UpperCase()

Dim rng As Range

For Each rng In Range("B2", Range("B" & Rows.Count).End(xlUp))
On Error Resume Next
    rng = StrConv(rng.Text, vbUpperCase)
Next

Sheet1.Range("A2", Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp)).Sort Sheet1.[A2], 1

On Error GoTo 0

End Sub
It is actually two codes with the first one calling the second one. After writing the first code, I thought that you may want to change any lower case letter entries (of the serial numbers) to uppercase and then sort the products alphabetically (just for easier reference) so I just added the second code.
I have assumed that the product names are in Column A with the serial numbers being created in Column B.
Line 8 of the above code inserts a formula in Column B for each entry in Column A. The formula takes the first two letters of the product name in Column A and adds the numeric part of the serial number (I have just chosen 1000 as the starting point so the first number will be 1002 as it is in row 2).
Lines 24 and 25 in the code above do the sorting but you may have to change the "B" to the letter of your last column of data.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/sqfr49ejeqqwezo/Create%20serial%20numbers%2C%20sort.xlsm?dl=0
Click on the "Try Me" button to see it all work. If you like, add more product names in Column A then click on the button again to see how the code re-adjusts.
I hope that this helps.
Cheerio,
vcoolio.
P.S.: If, eventually, you wish to create barcodes for your products, read the following article which will advise you on how it can be done including the downloads that you will need to set the application up.
http://www.myonlinetraininghub.com/create-barcodes-with-excel-vba

vcoolio · Answer

Hello JC0389,
I have revamped the codes as follows for you:-
- The serial number (unique IDs) will be created automatically once you enter a value in Column A without having to click on a button. This includes addition of a dash.
- I have removed the Sort function and added a Filter function instead. This way, as your list grows, you can place a value from Column A in the Filter box (cell D1) then click on "GO" and all data for the selected value will be filtered and displayed for you to analyse.
- I have added a "Filter Off" button for once you are done analysing.
So, all the following codes go into a standard module:-
Sub CreateSerialNos()

Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

With Range("B2:B" & lr)
        .Formula = "=Left(A2,2) & "" -""&Row()+1000"
    End With
    
UpperCase

End Sub

Sub UpperCase()

Dim rng As Range

For Each rng In Range("B2", Range("B" & Rows.Count).End(xlUp))
On Error Resume Next
    rng = StrConv(rng.Text, vbUpperCase)
Next

On Error GoTo 0

End Sub
Sub Filter()

Dim fSrch As String
fSrch = [D1].Value

Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, fSrch
[D1].Value = "Filter"

End Sub
Sub FilterOff()

With Sheet1
.[A1].AutoFilter
End With

End Sub
The following code needs to be placed in the worksheet module:-
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
CreateSerialNos

End Sub
This code is the one which allows you to type a value in Column A (Nike, Adidas etc...) and then when you click away (or press enter or down arrow) the IDs will be created in Column B.
To implement this sheet code, right click on the Sheet1 tab (or whatever your sheet is named), select "view code" from the menu that appears and then, in the big white field that then appears, paste the code.
The subroutine named "UpperCase" is probably not really necessary but.....................
In your actual workbook, the subroutine named "Filter" needs to be assigned to the "GO" button and the subroutine named "FilterOff" needs to be assigned to the "Filter Off" button.
Following is the link to my updated test workbook:-
https://www.dropbox.com/s/eosp007ugsutzvy/Create%20serial%20numbers%2C%20filter.xlsm?dl=0
Type a value in Column A at the bottom of the existing values then click away (or press enter or down arrow) to see the codes at work.
Make sure that you test all the above in a copy of your actual workbook first.
I hope that this helps.
Cheerio,
vcoolio.

Unique identifier

2 responses

Similar discussions

Subscribe To Our Newsletter!