Generate the next number on these sequence

[mark692]

hi guys is there a formula that will generate the next number on these sequence,
the sequence will be like this
ex.
40120-1
40120-2
40120-3
40120-4
and so on and so on

40120 is tha date today
the number after 40120 is the sequence i want to change every time i add another transaction

 

[Peter_SSs]

there are times that i need to put the same transaction number in the column

Hmm, no mention of that before in either description or examples.

Here are a few other options to consider, including a user-defined function since you said vba was acceptable.

Function NextNum(rTansactions As Range) As String
  Dim vSplit As Variant
  Dim sPrefix As String
  
  sPrefix = Format(Date, "mmddyy-")
  NextNum = sPrefix & 1
  On Error Resume Next
  NextNum = sPrefix & Split(rTansactions.Find(What:=sPrefix, LookAt:=xlPart, SearchDirection:=xlPrevious), "-")(1) + 1
End Function

mark692 2020-04-02 1.xlsm
	B	C	D	E	F
1
2	Next Transaction Code			Transaction	Cash
3	040220-4			040220-1
4	040220-4			040220-2
5	040220-4			040220-2
6				040220-3
7
8
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TEXT(TODAY(),"mmddyy-")&IF(COUNTIF(Table4[Transaction],TEXT(TODAY(),"mmddyy")&"*"),MID(LOOKUP("z",Table4[Transaction]),8,99)+1,1)
B4	B4	=TEXT(TODAY(),"mmddyy-")&IF(COUNTIF(E3:E1000,TEXT(TODAY(),"mmddyy")&"*"),MID(LOOKUP("z",E:E),8,99)+1,1)
B5	B5	=NextNum(Table4[Transaction])

 

[mark692]

Hmm, no mention of that before in either description or examples.

Here are a few other options to consider, including a user-defined function since you said vba was acceptable.

Function NextNum(rTansactions As Range) As String
  Dim vSplit As Variant
  Dim sPrefix As String
 
  sPrefix = Format(Date, "mmddyy-")
  NextNum = sPrefix & 1
  On Error Resume Next
  NextNum = sPrefix & Split(rTansactions.Find(What:=sPrefix, LookAt:=xlPart, SearchDirection:=xlPrevious), "-")(1) + 1
End Function

mark692 2020-04-02 1.xlsm
	B	C	D	E	F
1
2	Next Transaction Code			Transaction	Cash
3	040220-4			040220-1
4	040220-4			040220-2
5	040220-4			040220-2
6				040220-3
7
8
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TEXT(TODAY(),"mmddyy-")&IF(COUNTIF(Table4[Transaction],TEXT(TODAY(),"mmddyy")&"*"),MID(LOOKUP("z",Table4[Transaction]),8,99)+1,1)
B4	B4	=TEXT(TODAY(),"mmddyy-")&IF(COUNTIF(E3:E1000,TEXT(TODAY(),"mmddyy")&"*"),MID(LOOKUP("z",E:E),8,99)+1,1)
B5	B5	=NextNum(Table4[Transaction])

i tried it and i thinks this is better it also give transaction number even if my column is empty! thank you!

 

[Peter_SSs]

it also give transaction number even if my column is empty!

Yes, but Rick's formula also does that.

thank you!

You're welcome.