Adding serial number

[Gwilliams57]

I need to change my ERP system to a serialized inventory system, i have list of products and their total quantity. all i am trying to do is take a product and if the total quantity is 10, create 10 records so i can assign a unique number to each product.

Example of data i have:

2018 Catalog

10

<tbody>
</tbody>

can i do this in excel ( i am so bad in excel), i currently have 2075 products with a 125k total.

i could use some help

thank you


EDIT: Same question posted in Access forum here: https://www.mrexcel.com/forum/microsoft-access/1045376-adding-serial-number.html

 

[MartyS]

Hi,

Try this: https://www.dropbox.com/s/ymb35tsavk8iijf/GWilliams 57-Adding Serial Number.xlsm?dl=0



It takes your data from Sheet1 and creates [Qty] x number of items, per item in Sheet 2.


Below is the code

NB: Make sure you don't want anything in SHEET2 before running, as it clears the contents of that sheet in order to put new data into it!

Sub test()


    Const SrcSheet = "Sheet1"
    Const TgtSheet = "Sheet2"


    Dim rng As Range, Data()
    Dim LastRow As Long, TgtMrkRow As Long, Cntr As Long, QtyCntr As Long
    
    LastRow = Sheets(TgtSheet).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Sheets(TgtSheet).Range("2:" & LastRow).ClearContents
    
    LastRow = Sheets(SrcSheet).Range("A:B").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Set rng = Sheets(SrcSheet).Range("A2:B" & LastRow)
    
    Data = rng
    
    TgtMrkRow = 1
    For Cntr = 1 To UBound(Data)
        For QtyCntr = 1 To Data(Cntr, 2)
            TgtMrkRow = TgtMrkRow + 1
            Sheets(TgtSheet).Range("A" & TgtMrkRow) = Data(Cntr, 1)
            Sheets(TgtSheet).Range("B" & TgtMrkRow) = QtyCntr
            Sheets(TgtSheet).Range("C" & TgtMrkRow) = Data(Cntr, 2)
        Next QtyCntr
    Next Cntr
    
    MsgBox "Done"


End Sub

 

[Gwilliams57]

That's amazing, thank you so much. i have 11 warehouse to convert and you made the first step so easy.

 

[MartyS]

You're welcome!