• 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...

  • 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

Rajendar

Member
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.
 

Attachments

  • YEAR OF 2017.xlsx
    124.4 KB · Views: 15
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
 

Attachments

  • YEAR OF 2017 SerialNumber.xlsx
    184.3 KB · Views: 17
Last edited:
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
 
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
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 !
 
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
 
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
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
 
For VBA try below code.

Code:
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.
 
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
 

Attachments

  • YEAR OF 2017 SerialNumber.xlsx
    193.2 KB · Views: 4
Something like below in worksheet module.
Code:
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.
 

Attachments

  • YEAR OF 2017 SerialNumber.xlsb
    199.3 KB · Views: 10
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
 
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.
 
Back
Top