Duplicate Timestamps & Creation of Unique Reference Number based on Loads

[LT TASL]

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.

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

 

[Ezguy4u]

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
	A	B	C
1	ID	Start Journey Timestamps	Load ID
2	26192	01/01/2018 01:32
3	251603	01/01/2018 05:37
4	265422	01/01/2018 06:25
5	265553	01/01/2018 06:25
6	251606	01/01/2018 07:13
7	261507	01/01/2018 07:23
8	262061	01/01/2018 12:10
9	261507	01/01/2018 12:12
10	265598	01/01/2018 13:15
11	265617	01/01/2018 13:15
12	262074	01/01/2018 13:24
13	262081	01/01/2018 13:30
Duplicate

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
	A	B	C
1	ID	Start Journey Timestamps	Load ID
2	26192	01/01/2018 01:32	1
3	251603	01/01/2018 05:37	2
4	265422	01/01/2018 06:25	3
5	265553	01/01/2018 06:25	3
6	251606	01/01/2018 07:13	4
7	261507	01/01/2018 07:23	5
8	262061	01/01/2018 12:10	6
9	261507	01/01/2018 12:12	7
10	265598	01/01/2018 13:15	8
11	265617	01/01/2018 13:15	8
12	262074	01/01/2018 13:24	9
13	262081	01/01/2018 13:30	10
Duplicate