generate item number based on product category

[Mohzein]

hello dears, hope you are all well and safe
i dont know if this question was asked before, but i hope i get your help!

i have created a user form to create item list with several combo box contain a specified category for each item, ex:

CMB product type: T-Shirt or Short
CMB Material: Cotton or Wool

what i am trying to do is to generate an item number containing the first 3 digits of the product type and the fist 2 digits of the material followed by 00001, ex:

for T-Shirt made of Cotton, the item number should be TSHCO00001
for T-Shirt made of Wool, the item number should be TSHWO00001
for Shorts made of Cotton, the item number should be SHOCO00001

and so on

but this must be incremented whenever creating a new item, ex:

first item number of T-Shirt made of Cotton was TSHCO00001 , the next one should be TSHCO00002, and so on

i have the item number in column B, type in column C, and material in column G

your help and support is much appreciated!!

 

[Alex Blakenburg]

This should do what you are trying to do.

Note:
1) The Countifs formula is anchored on the first row where it is row $2:2 and then expands ($2:3, $2:4) as you copy it down so the count increases as it encounters the same product again.
2) If you convert the list into an Excel table the formula will auto-populate as you add more rows.
3) You only have a Dash in the text at the moment but I have catered for a space in the Product Type as well just in case,

20210510 Generat Item Number.xlsx
	A	B	C
1	Item Number	CMB Product Type	CMB Material
2	TSHWO00001	T-Shirt	Wool
3	SHOCO00001	Short	Cotton
4	TSHCO00001	T-Shirt	Cotton
5	SHOCO00002	Short	Cotton
6	TSHWO00002	T-Shirt	Wool
7	SHOCO00003	Short	Cotton
8	TSHCO00002	T-Shirt	Cotton
9	SHOCO00004	Short	Cotton
10	SHOWO00001	Short	Wool
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=UPPER( LEFT(SUBSTITUTE(SUBSTITUTE($B2,"-","")," ",""),3)& LEFT($C2,2))& TEXT(COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2),"00000")

 

[Mohzein]

Dear Alex,

Thank you so much for your help, but how to implement this in VBA, as mentioned, i created a user form using VBA

here is below the user form and all the codes i have created, what i am trying to do is once i press "Add" , a new item number should be generated in the item number field based on the type and material as previously explained

Option Explicit

Private Sub cmb_Material_Change()


End Sub

Private Sub cmb_Product_type_Change()


End Sub

Private Sub CommandButton10_Click()

Dim nwb As Workbook
Set nwb = Workbooks.Add

ThisWorkbook.Sheets("ITEM_LIST").UsedRange.Copy nwb.Sheets(1).Range("A1")


End Sub



Private Sub CommandButton8_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ITEM_LIST")



''''''update data''''''

Dim lr As Integer
lr = Me.TXT_ID.Value

sh.Range("A" & lr + 1).Value = lr
sh.Range("b" & lr + 1).Value = Me.TXT_ITEM_NUMBER.Value
sh.Range("c" & lr + 1).Value = Me.cmb_Product_type.Value
sh.Range("d" & lr + 1).Value = Me.cmb_Size.Value
sh.Range("e" & lr + 1).Value = Me.cmb_Model.Value
sh.Range("f" & lr + 1).Value = Me.cmb_Gender.Value
sh.Range("g" & lr + 1).Value = Me.cmb_Material.Value
sh.Range("h" & lr + 1).Value = Me.cmb_origin.Value
sh.Range("i" & lr + 1).Value = Me.cmb_vendor.Value
sh.Range("j" & lr + 1).Value = Me.TXT_VEND_ITEM_NUM.Value
sh.Range("k" & lr + 1).Value = Me.TXT_UC.Value
sh.Range("l" & lr + 1).Value = Me.TXT_SP.Value



''''''clear data''''''

Me.cmb_Product_type.Value = ""
Me.cmb_Size.Value = ""
Me.cmb_Model.Value = ""
Me.cmb_Gender.Value = ""
Me.cmb_Material.Value = ""
Me.cmb_origin.Value = ""
Me.cmb_vendor.Value = ""
Me.TXT_VEND_ITEM_NUM.Value = ""
Me.TXT_UC.Value = ""
Me.TXT_SP.Value = ""
Me.TXT_ITEM_NUMBER.Value = ""
Me.TXT_ID.Value = ""

Call show_data

MsgBox "Product Was successfully Updated", vbInformation


End Sub

Private Sub CommandButton9_Click()


''''''check duplication'''''

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ITEM_LIST")

If Application.WorksheetFunction.CountIf(sh.Range("l:l"), Me.TXT_ITEM_NUMBER.Value) > 0 Then
MsgBox "Item Number Already Exsit", vbInformation

Exit Sub
End If


''''''add data''''''

Dim lr As Integer
lr = Application.WorksheetFunction.CountA(sh.Range("a:a"))

sh.Range("A" & lr + 1).Value = lr
sh.Range("b" & lr + 1).Value = Me.TXT_ITEM_NUMBER.Value
sh.Range("c" & lr + 1).Value = Me.cmb_Product_type.Value
sh.Range("d" & lr + 1).Value = Me.cmb_Size.Value
sh.Range("e" & lr + 1).Value = Me.cmb_Model.Value
sh.Range("f" & lr + 1).Value = Me.cmb_Gender.Value
sh.Range("g" & lr + 1).Value = Me.cmb_Material.Value
sh.Range("h" & lr + 1).Value = Me.cmb_origin.Value
sh.Range("i" & lr + 1).Value = Me.cmb_vendor.Value
sh.Range("j" & lr + 1).Value = Me.TXT_VEND_ITEM_NUM.Value
sh.Range("k" & lr + 1).Value = Me.TXT_UC.Value
sh.Range("l" & lr + 1).Value = Me.TXT_SP.Value



''''''clear data''''''

Me.cmb_Product_type.Value = ""
Me.cmb_Size.Value = ""
Me.cmb_Model.Value = ""
Me.cmb_Gender.Value = ""
Me.cmb_Material.Value = ""
Me.cmb_origin.Value = ""
Me.cmb_vendor.Value = ""
Me.TXT_VEND_ITEM_NUM.Value = ""
Me.TXT_UC.Value = ""
Me.TXT_SP.Value = ""
Me.TXT_ITEM_NUMBER.Value = ""

Call show_data

MsgBox "Product Was successfully Added", vbInformation


End Sub


Sub show_data()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ITEM_LIST")

Dim lr As Integer
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
.ColumnCount = 12
.ColumnHeads = True
.ColumnWidths = "0,60,60,60,60,60,60,60,60,45,45,45"
.RowSource = "ITEM_LIST!A2:L" & lr


End With


End Sub

Private Sub Label1_Click()

End Sub

Private Sub ITEM_NUMBER_LBL_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.TXT_ID.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.TXT_ITEM_NUMBER.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.cmb_Product_type.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.cmb_Size.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.cmb_Model.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.cmb_Gender.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.cmb_Material.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
Me.cmb_origin.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
Me.cmb_vendor.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
Me.TXT_VEND_ITEM_NUM.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)
Me.TXT_UC.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
Me.TXT_SP.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)



End Sub

Private Sub TXT_ID_Change()

End Sub

Private Sub TXT_ITEM_NUMBER_Change()

End Sub

Private Sub UserForm_Activate()
Call show_data
End Sub
Private Sub UserForm_Initialize()


'''''drop down list product type'''''
With Me.cmb_Product_type
.AddItem "T-Shirt"
.AddItem "Shorts"
.AddItem "Pants"
.AddItem "Shirts"
.AddItem "Boxers"
.AddItem "Pants"
.AddItem "Dresses"
.AddItem "Skirts"
.AddItem "Pyjama"
.AddItem "Sweater"
.AddItem "Hoodys"
End With

'''''drop down list size'''''
With Me.cmb_Size
.AddItem "X-Small"
.AddItem "Small"
.AddItem "Medium"
.AddItem "Large"
.AddItem "X -Large"
.AddItem "2 X -Large"
.AddItem "3 X -Large"
.AddItem "4 X -Large"
.AddItem "5 X -Large"
.AddItem "New Born"
.AddItem "0-3 MTHS"
.AddItem "3-6 MTHS"
.AddItem "6-9 MTHS"
.AddItem "9-12 MTHS"
.AddItem "12-18 MTHS"
.AddItem "18-24 MTHS"
.AddItem "2-3 YRS"
.AddItem "3-4 YRS"
.AddItem "4-5 YRS"
.AddItem "5-6 YRS"
.AddItem "6-7 YRS"
.AddItem "7-8 YRS"
.AddItem "8-9 YRS"
.AddItem "9-10 YRS"
.AddItem "10-11 YRS"
.AddItem "11-12 YRS"
.AddItem "12-13 YRS"
.AddItem "13-14 YRS"
.AddItem "14-15 YRS"
End With

'''''drop down list model'''''
With Me.cmb_Model
.AddItem "Long Sleeve"
.AddItem "Short Sleeve"
.AddItem "No Sleeve"
End With

'''''drop down list gender'''''
With Me.cmb_Gender
.AddItem "Men"
.AddItem "Women"
.AddItem "Kids Boys"
.AddItem "Kids Girls"
.AddItem "New Born"
End With

'''''drop down list'''''
With Me.cmb_Material
.AddItem "Cotton"
.AddItem "Wool"
.AddItem "Denim"
.AddItem "Silk"
.AddItem "Polyester"
End With

'''''drop down list origin'''''
With Me.cmb_origin
.AddItem "Lebanon"
.AddItem "Turkey"
.AddItem "Egypt"
.AddItem "Syria"
.AddItem "Morocco"
End With

End Sub






Private Sub VENDOR_ITEM_NUMBER_LBL_Click()

End Sub


one thing i should make it clear, i have very limited knowledge in VBA, as i started to watch tutorials since 1 month ago only :$ so please bear with me :$

thank you again for your help!

 

[Alex Blakenburg]

You can try the below function to get the unique code.

My expectation is that you will need to get the value after the user has entered in the Product Type and Material you would then do something like this to get the ID.

    Dim ID As String    
    ID = GetItemID(Me.cmb_Product_type.Value,  Me.cmb_Material.Value)

Function GetItemID(ProdType As String, Material As String) As String
    Dim rng As Range
    Dim ItemBaseID As String
    Dim ItemUniqueID As String
    Dim UniqueNo As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ItemBaseID = Left(Replace(ProdType, "-", ""), 3)
    ItemBaseID = UCase(ItemBaseID & Left(Material, 2))
    Set rng = Range("B:B").Find(what:=ItemBaseID, after:=Cells(1, 2), LookIn:=xlValues, searchdirection:=xlPrevious, LookAt:=xlPart)
    If Not rng Is Nothing Then
        UniqueNo = Val(Right(rng.Value, 3)) + 1
    Else
        UniqueNo = 1
    End If
    ItemUniqueID = ItemBaseID & Format(UniqueNo, "000")
    GetItemID = ItemUniqueID
    
End Function