Help With Serial Number Sequential Orders in a Multiple Serial Number doc

[brsnyder1]

I have a document that I track multiple serial numbers of reports. The trick is that reports initiated by our company have a unique serial number (N00164-18-0001) and reports that are initiated by outside users have a different serial number so the excel spreadsheet has mixed reports going down the list. When I initiate a new report, I have to go back through all the report numbers and verify the next sequential report serial number (N00164-18-0002). I want to make a "Next Report Serial Number" next to my table and I'm looking to see if there is a way to sequentially be able to see what the next report number should be. I only need to track our companies next report serial number. I hope this makes sense and someone has the answer on how to sequentially generate the next serial number even if the report numbers have different serial numbers mixed in with them. Thank you!

 

[brsnyder1]

ttt

 

[brsnyder1]

Maybe I can simplify the question so I can get some answers.

I have a list of serial numbers
N00164-18-001
R90794-18-0024
N00164-18-002
V35053-18-2003

List continues to go on in cronological order as the reports come in. All the N00164 reports are serial numbers that we initiate. I need to be able to see what the next serial number is in sequence and have it auto populate either in a cell off to the side that is labeled "Next Serial" or when I begin to type N00164 in the cell then it auto generates in the box with the next sequential number. Trying to alleviate having to filter back through all the report numbers to find the last one our company initated.

 

[MickG]

Try this "Double Click Event" in column "A"
To load code:-
Right click sheet "Tab", from DropDown select "View Code", vbwindow appears.
Paste code into VbWindow, Close Vbwindow.

To run code Double Click empty cell in columns "A"
NB:-There must be at least on cell in column"A" with a "N00164" serial number.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
 [COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant

 [COLOR="Navy"]If[/COLOR] Target.Column = 1 And Target.Value = "" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
           [COLOR="Navy"]If[/COLOR] Left(Dn.Value, 6) = "N00164" [COLOR="Navy"]Then[/COLOR]
              temp = Dn.Value
            [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Dn
    Sp = Split(temp, "-")
    Target.Value = Sp(0) & "-" & Sp(1) & "-" & Format(Sp(2) + 1, "000")
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jtakw]

Hi,

You can either Manually type the Next Serial as identified by D2, or Copy/Paste Value from D2 in Column A, D2 will auto populate the Next Serial as you Add:

Book1
	A	B	C	D
1	N00164-18-001			Next Serial Report #
2	R90794-18-0024			N00164-18-004
3	N00164-18-002
4	V35053-18-2003
5	N00164-18-003
Sheet357

Cell Formulas
Range	Formula
D2	="N00164-18-"&TEXT(COUNTIF(A1:A100,"N00164-18*")+1,"000")

 

[brsnyder1]

I'm an Excel newbie, could you possibly break it down for me? Sorry! I'm having trouble trying to figure out how to load the code

 

[brsnyder1]

So if my serial number currently is at N00164-18-5001 how do i start the function to count from 5001? does that change the formula?

 

[jtakw]

Would this do it for you?

Book1
	A	B	C	D
1	N00164-18-5001			Next Serial Report #
2	R90794-18-0024			N00164-18-5004
3	N00164-18-5002
4	V35053-18-2003
5	N00164-18-5003
Sheet357

Cell Formulas
Range	Formula
D2	="N00164-18-5"&TEXT(COUNTIF(A1:A100,"N00164-18-5*")+1,"000")

 

[shg]

	A​	B​
2​	N00164-18-5001	<< Input
3​	N00164-18-5002	<< Dragged down
4​	N00164-18-5003
5​	N00164-18-5004
6​	N00164-18-5005
7​	N00164-18-5006
8​	N00164-18-5007
9​	N00164-18-5008

 

[KolGuyXcel]

Adjust your range in the formula accordingly!

In the formula in # 5 the range is assumed to be until 100th row! But I guess 100000 would be sufficient! So use the formula as

="N00164-18-"&TEXT(COUNTIF(A1:A100000,"N00164-18*")+1,"0000")