1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto serial number

Discussion in 'VBA Macros' started by Rajendar, Dec 22, 2016.

  1. Rajendar

    Rajendar Member

    Messages:
    36
    Hi,

    I need any one help to create auto serial number as per the below conditions sheet is attached.


    Column I5:I12000 – I Need to auto generated the serial numbers in “I5”


    If there is item code, Job number, issue date if it is available based on that I need to apply the formulas,

    If the job number and date is same then next serial number has to be generate automatically.

    The Logic is

    First condition: If “O” column having the item code, generate the serial number in "I5" starting number is like MIVS-0001 ( MIVS-) Is common for all. Like MIVS-0001,

    Second condition: if there is no item code in "O"not to generate any serial numbers

    Third condition: column number “J & M” “ Issue date & Job number ” is same within the date then generate the serial number same like MIVS-0001, If the date or Job number is different Generate the serial number as next MIVS-0002.


    I Request you to please create formula or VBA without any errors with the conditions like N/A should be blank.

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,053
    Try,

    In I5, enter formula :

    =IF(O5="","",IF(COUNTIFS(J$5:J5,J$5:J5,M$5:M5,M$5:M5)=1,MAX(I$4:I4)+1,LOOKUP(9^9,I$4:I4/(J$4:J4=J5)/(M$4:M4=M5))))

    and,

    in I5 >> Custom Cells Format >>

    type box enter : "MIVS-"0000 >> OK

    All copy down

    Regards
    Bosco

    Attached Files:

    Last edited: Dec 22, 2016
  3. Rajendar

    Rajendar Member

    Messages:
    36
    Dear Bosco,

    :) Great its working fine.

    Thanks for your help.

    The same can we put it in VBA ?. I Request you to please try in VBA. the reason why i am asking in VBA. I Have huge data to be enter almost i use this up to 20000 cells.

    Request you to Please help in VBA
  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,053
    Hi,

    I am not familiar to VBA, please transfer your question to the VBA/Marco of this Forum.

    Regards
    Bosco

    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to VBA forum !

  5. rahulshewale1

    rahulshewale1 Member

    Messages:
    82
    hii bosco sir,

    can you please explain me lookup formula ,why you sir use 9^9.

    =IF(O5="","",IF(COUNTIFS(J$5:J5,J$5:J5,M$5:M5,M$5:M5)=1,MAX(I$4:I4)+1,LOOKUP(9^9,I$4:I4/(J$4:J4=J5)/(M$4:M4=M5))))


    Thanks
    Rahul shewale
    Thomas Kuriakose likes this.
  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,053
    Hi,

    This is a 2 criteria Lookup formula, which is designed to return the last match value.

    LOOKUP(9^9,I$4:I4/(J$4:J4=J5)/(M$4:M4=M5)) is same as

    LOOKUP(2,1/((J$4:J4=J5)*(M$4:M4=M5)),I$4:I4)

    9^9 is equal to 387,420,489 and is the BigNum of the formula.

    Try Google to search for BigNum in Excel formula, there will have a lot of examples found.

    Regards
    Bosco
  7. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,231
    For VBA try below code.

    Code (vb):
    Sub AssignMivNo()
    Dim dict As Object
    Dim i As Long
    Dim mivN As Integer: mivN = 0
    Dim resArray

    Set dict = CreateObject("Scripting.Dictionary")

    myArray = Range("J5:O" & Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim resArray(UBound(myArray, 1), 1)
    For i = 1 To UBound(myArray, 1)
        If myArray(i, 1) <> "" And myArray(i, 4) <> "" And myArray(i, 6) <> "" Then
            If dict.exists(myArray(i, 1) & myArray(i, 4)) Then
                resArray(i - 1, 0) = "MIVS-" & Format(dict.Item(myArray(i, 1) & myArray(i, 4)), "0000")
            Else
                mivN = mivN + 1
                dict.Add Key:=myArray(i, 1) & myArray(i, 4), Item:=mivN
                resArray(i - 1, 0) = "MIVS-" & Format(mivN, "0000")
            End If
        Else
            resArray(i - 1, 0) = ""
        End If
    Next i

    Range("I5").Resize(UBound(resArray, 1)) = resArray
    End Sub
    NOTES:
    1. This assumed Column A will have value present whenever there's any data in range J:O. Adjust as needed.
    2. This will actually input text string "MIVS-####" instead of just the number with custom formatting.
    Thomas Kuriakose likes this.
  8. Rajendar

    Rajendar Member

    Messages:
    36
    Dear Chihiro,

    Yes its working fine. thanks for your help.

    but there should be some alert is better i think. when there is no job number ref column cell "M" if i put blank. alert is msg box is " Please enter Job number " I tried with validation alert but when copy the code from other excel sheet that function is not working. is it possible to put it in vba is great for me.

    when i enter or paste the part number in "O" cells with out job number msg box is required.

    Please help me

    Attached Files:

  9. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,231
    Something like below in worksheet module.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("O5:O65536")) Is Nothing Then
        If Target.Value <> "" And Target.Offset(, -2).Value = "" Then
            MsgBox "Missing Job Number!", vbCritical
            Target.Offset(, -2).Select
        End If
    End If

    End Sub
    See attached.

    Attached Files:

    Thomas Kuriakose likes this.
  10. Rajendar

    Rajendar Member

    Messages:
    36
    Hi,
    Chihiro,

    i am getting msg box when i type the part number with out entering the job number

    but when i copy and paste the part number that time i am not getting message box
  11. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,231
    Are you copying more than 1 cell at a time? If so the code is meant to check when Target <=1.

    I'd not recommend doing worksheet change event for more than 1 cell at a time, unless you are absolutely sure of how users are editing range.

Share This Page