This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function.
You may also want to read:
Excel check duplicate values using Conditional Formatting
Excel delete duplicated data in consecutive rows
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.
