Duplicate Timestamps & Creation of Unique Reference Number based on Loads


New Member
Oct 23, 2020
Office Version
  1. 2019
  1. Windows
  2. Web
Good Afternoon,

I am having some issues with a dataset that came from an outdated system our business used before I joined, they had recently moved to a new system when I joined and now I can see why!

The data below relates to Customer Journeys, which lack a vehicle identifiable field (which would've made what I want to do much easier). But what I am essentially trying to do is create a "Load ID" for each Journey based on the amount of customers on each load.

The below query relates to Journeys & Customers:
Column A includes Unique Reference number for the Customer's Journey
Column B Includes a date-timestamp for when the customer's Journey began
Column C to include a sequential ID Reference number which is unique to the LOAD

If there is a duplicate in Column B, I am expecting Column C to be the same for each unique set of duplicates within the data set. If there is no duplicates I still want Column C to be populated with a sequential Reference number.
The Below is a snip of what I would like the data to look like on a small number of Journeys, there is around 250k rows to apply this mechanism to.
Snip for MrExcel.PNG

Row 4 & 5's journeys travelled on the Same LOAD which means 2 customers travelled and therefore have the same LOAD ID, where there are no duplicates in Column B the customers travelled alone. There is no theoretical maximum amount of customers than can be on any one LOAD.

Its been a long time since I have used any VBA, can anyone assist with some code that will remove the manual solution?

Thanks in advance,


Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce


Board Regular
Feb 10, 2010
Office Version
  1. 2019
  1. Windows
Let's get the ball rolling. I want to make sure this is what your want. The first worksheet is the input. What you will be starting with. Then the program. The bottom worksheet is the out put. The end result. Now to run this program with 250K rows might be a problem. But at least this will be a start and maybe an A Student will weigh in with a better program. So let the games begin.

1IDStart Journey TimestampsLoad ID
22619201/01/2018 01:32
325160301/01/2018 05:37
426542201/01/2018 06:25
526555301/01/2018 06:25
625160601/01/2018 07:13
726150701/01/2018 07:23
826206101/01/2018 12:10
926150701/01/2018 12:12
1026559801/01/2018 13:15
1126561701/01/2018 13:15
1226207401/01/2018 13:24
1326208101/01/2018 13:30

VBA Code:
Sub Dup1()

Dim LastRow As Long
Dim Row1 As Long
Dim Cnt1 As Long

Cnt1 = 1
LastRow = Cells(Rows.Count, 1).End(3).Row

For Row1 = 2 To LastRow
Cells(Row1, 3) = Cnt1

If Cells(Row1, 2) = Cells(Row1 + 1, 2) Then

Row1 = Row1 + 1
Cells(Row1, 3) = Cnt1

End If
Cnt1 = Cnt1 + 1

Next Row1

End Sub

1IDStart Journey TimestampsLoad ID
22619201/01/2018 01:321
325160301/01/2018 05:372
426542201/01/2018 06:253
526555301/01/2018 06:253
625160601/01/2018 07:134
726150701/01/2018 07:235
826206101/01/2018 12:106
926150701/01/2018 12:127
1026559801/01/2018 13:158
1126561701/01/2018 13:158
1226207401/01/2018 13:249
1326208101/01/2018 13:3010

Watch MrExcel Video

Forum statistics

Latest member