Excel assign sequence number to duplicate records

This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function.

Excel assign sequence number to duplicate records

Suppose you have two sales transactions on the same date but you don’t have any transaction timestamp (but records are in ascending order of time), you may want to add a number to each transaction so that you can easily identify and filter the first transaction, 2nd transaction, etc. Assigning sequence number can also facilitate to use SQL to select the record using Min and Max; otherwise it is easy to create Cartesian Product.

Example

Let’s say we have the below transaction dates. Note that Jan 1st is repeated at the bottom.

 

In Cell B2, type formula  =COUNTIF($A$2:A2,A2)

Autofill down the column, then you will get a sequence number for each transaction.

That means if you have one transaction on a date, you will always get sequence number 1. If you have two transactions on the same date, the first record is assigned sequence number 1, then second record gets number 2 and so on. Therefore Jan 1st 2017 in row 1 is assigned sequence number 1 because it comes first in the data set, then row 14 is assigned sequence number 2.

Assign sequence number to duplicate records – Excel VBA

While it is good enough to follow the above non-VBA solution, you can also go for a Macro solution which uses the same COUNTIF formula.

Press ALT+F11, copy and paste the below code in a new Module.

Public Sub assignSeq()
 targetRng = "A2:A14" 'Define the Range you want to assign number
 For Each Rng In Range(targetRng)
 Rng.Offset(0, 1).Value = Application.WorksheetFunction.CountIf(Range(Split(targetRng, ":")(0) & ":" & Rng.Address), Rng.Value)
 Next
End Sub

Run the Macro to get the same result without seeing the formula.

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *