Duplicate Timestamps & Creation of Unique Reference Number based on Loads

LT TASL

New Member
Joined
Oct 23, 2020
Messages
1
Office Version
  1. 2019
Platform
  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,

LT
 

Some videos you may like

Excel Facts

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

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
119
Office Version
  1. 2019
Platform
  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.

Book1
ABC
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
Duplicate


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

Book1
ABC
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
Duplicate
 

Watch MrExcel Video

Forum statistics

Threads
1,113,980
Messages
5,545,322
Members
410,676
Latest member
M0J0jojo
Top