Auto generate a sequence

D

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Hi,
I hope someone can help me here. I would like to set a auto contract number. I want to check the CO number if it is duplicate and then add the -n to the contract no. If it is not a duplicate it must be the next sequence in the numbering. Below is an example of what I would like to achieve. I need to do this for 60K CO numbers.

A B
1 CO number Contracts No
2 L010701698 PP19/20/0001-1
3 L010701698 PP19/20/0001-2
4 L010701698 PP19/20/0001-3
5 L010701698 PP19/20/0001-4
6 L010701698 PP19/20/0001-5
7 L010706796 PP19/20/0002-1
8 L010706796 PP19/20/0002-2
9 L010706804 PP19/20/0003
10 L010706887 PP19/20/0004
11 L010707919 PP19/20/0005
 
Sort by date Sort by votes

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Question... will your Contract Numbers always start with PP19/20 as Marcelo's code assumes? If not, here is a macro that is not dependent on knowing the format for the Contract Numbers (nor for the CO Numbers either), although as with Marcelo's code, it does depend on the data being grouped together as your example shows...
Code: 
[table="width: 500"]
[tr]
	[td]Sub IndexDuplicateContractNumbers()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A2:B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
  For R = 1 To UBound(Data) - 1
    If Data(R, 1) & " " & Data(R, 2) = Data(R + 1, 1) & " " & Data(R + 1, 2) Then
      X = X + 1
      Data(R, 2) = Data(R, 2) & "-" & X
    ElseIf Data(R, 1) & " " & Data(R, 2) = Left(Data(R - 1, 1) & " " & Data(R - 1, 2), Len(Data(R, 1) & " " & Data(R, 2))) Then
      X = X + 1
      Data(R, 2) = Data(R, 2) & "-" & X
      X = 0
    Else
      X = 0
    End If
  Next
  Range("A2").Resize(UBound(Data), 2) = Data
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Rick

Using your code i'm getting blank results in column B. Am i missing something?
Data

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
CO number​
[/TD]
[TD]
Contracts No​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
L010706796​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
L010706796​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
L010706804​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
L010706887​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
L010707919​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
CO number​
[/TD]
[TD]
Contracts No​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
L010701698​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
L010706796​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
L010706796​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
L010706804​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
L010706887​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
L010707919​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.
 
Last edited:
Upvote 0
oh, i can see what happened

You assumed data like this.

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
CO number​
[/td][td]
Contracts No​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
L010706796​
[/td][td]
PP19/20/0002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
L010706796​
[/td][td]
PP19/20/0002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
L010706804​
[/td][td]
PP19/20/0003​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
L010706887​
[/td][td]
PP19/20/0004​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
L010707919​
[/td][td]
PP19/20/0005​
[/td][/tr]
[/table]

Am i right?

M.
 
Upvote 0
Marcelo Branco said:
oh, i can see what happened

You assumed data like this.

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
CO number​
[/td][td]
Contracts No​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
L010701698​
[/td][td]
PP19/20/0001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
L010706796​
[/td][td]
PP19/20/0002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
L010706796​
[/td][td]
PP19/20/0002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
L010706804​
[/td][td]
PP19/20/0003​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
L010706887​
[/td][td]
PP19/20/0004​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
L010707919​
[/td][td]
PP19/20/0005​
[/td][/tr]
[/table]

Am i right?

M.
Click to expand...

Yes, you are correct. I got the impression that the data is laid out as you show it above and that the OP wanted the duplicates indexed. The reason I assumed that is because the OP said this... "I want to check the CO number if it is duplicate and then add the -n to the contract no.", but in rereading the original message, I am not sure my interpretation is correct. You appear to be right... the OP appears to have asked for the number to be generated and, if necessary, indexed. I did not try you code, so I missed that it was doing that which, based on the OP's response in Message #10 , appears to be what was asked for.
 
Upvote 0
Rick Rothstein said:
Yes, you are correct. I got the impression that the data is laid out as you show it above and that the OP wanted the duplicates indexed. The reason I assumed that is because the OP said this... "I want to check the CO number if it is duplicate and then add the -n to the contract no.", but in rereading the original message, I am not sure my interpretation is correct. You appear to be right... the OP appears to have asked for the number to be generated and, if necessary, indexed. I did not try you code, so I missed that it was doing that which, based on the OP's response in Message #10 , appears to be what was asked for.
Click to expand...
Okay, just to keep the record "straight", here is how I written the code for this request (note the Contract prefix is specified at the beginning in case it needs to be changed in the future)...
Code: 
Sub IndexDuplicateContractNumbers()
  Dim R As Long, X As Long, Idx As Long, D As String, Prefix As String, Data As Variant
  [B][COLOR="#0000FF"]Prefix = "PP19/20/"[/COLOR][/B]
  Data = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim Preserve Data(1 To UBound(Data), 1 To 2)
  For R = 2 To UBound(Data)
    If Data(R, 1) <> Data(R - 1, 1) Then
      X = X + 1
      Data(R, 2) = Prefix & Format(X, "0000")
    ElseIf Not Data(R - 1, 2) Like "*-*" Then
      D = Data(R - 1, 2)
      Data(R - 1, 2) = D & "-1"
      Idx = 2
      Data(R, 2) = D & "-2"
    Else
      Idx = Idx + 1
      Data(R, 2) = Split(Data(R - 1, 2), "-")(0) & "-" & Idx
    End If
  Next
  Range("A1").Resize(UBound(Data), 2) = Data
End Sub
 
Last edited:
Upvote 0
Hi Rick,
Thank you for this it gives me more flexibility. It works perfect.
Rick and Marcelo you are both stars:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel to create new rows of data in a table
Replies
1
Views
76
mumps
mumps
R
  • Solved
Sequencing Numbers Not Working
Replies
4
Views
642
DanteAmor
DanteAmor
V
  • Solved
How to Create a Series that Counts Depending on Another Column
Replies
5
Views
309
Fluff
Fluff
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
236
shg
shg
I
  • Question Question
Search for catergory name and add 1 to the next cell
Replies
2
Views
453
instanceoftime
I

Forum statistics

Threads
1,223,958
Messages
6,175,642
Members
452,663
Latest member
MEMEH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top