Auto generate a sequence

[DHayes]

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

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[Rick Rothstein]

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

[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]

 

[Marcelo Branco]

Rick

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

	A​	B​
1​	CO number​	Contracts No​
2​	L010701698​
3​	L010701698​
4​	L010701698​
5​	L010701698​
6​	L010701698​
7​	L010706796​
8​	L010706796​
9​	L010706804​
10​	L010706887​
11​	L010707919​

<tbody>
</tbody>

After macro

	A​	B​
1​	CO number​	Contracts No​
2​	L010701698​
3​	L010701698​
4​	L010701698​
5​	L010701698​
6​	L010701698​
7​	L010706796​
8​	L010706796​
9​	L010706804​
10​	L010706887​
11​	L010707919​

<tbody>
</tbody>

M.

 

[Marcelo Branco]

oh, i can see what happened

You assumed data like this.

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

Am i right?

M.

 

[Rick Rothstein]

oh, i can see what happened

You assumed data like this.

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

Am i right?

M.

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.

 

[Rick Rothstein]

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.

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

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

 

[DHayes]

Hi Rick,
Thank you for this it gives me more flexibility. It works perfect.
Rick and Marcelo you are both stars