calculate if a car has made a return journey

Hi all

Im working on a work project to see if a car has made a return journey

There are a number of "trips", but for simplicity, ive made them just 2, LOBH (london to birmingham) and BHLO (Birmingham to London)
There is a date and time in seperate field of when the car passed a checkpoint
There is a unique booking ID which i think serves no purpose in this task as its not sequential
then there is the car registration

In this example there are only a few rows, but there are over 30k entries in the actual file.

I would like to somehow highlight cars that went to and came back from locations.

Not every car would return, and if a car does return it could be the same day or several days later.

Sorting the data in real life would not work so easily as there could be thousand entries before the return happens,

I would appreciate any help around this!

File attached

Thank you

Can you not just have another 2 columns for the return journey rather than a separate row?

Also, is your example just to show the data structure, and therefore not accurate? As rows 7 & 8 appear to show the same return journey?

Also, could you have a round trip like London to Birmingham, then Birmingham to Bristol, then Bristol to London (plus many other convoluted trips)?

Pete

Originally Posted by EchoPassenger

Can you not just have another 2 columns for the return journey rather than a separate row?

Also, is your example just to show the data structure, and therefore not accurate? As rows 7 & 8 appear to show the same return journey?

Hi, unfortunately journies are electronically registered and appear on seperate rows.

Data was for demo purposes only

Thank you

Originally Posted by Pete_UK

Also, could you have a round trip like London to Birmingham, then Birmingham to Bristol, then Bristol to London (plus many other convoluted trips)?

Pete

Hi Pete, only 2 and from journies. No round trips

Hi mate, I guess it needs to make the return journy at some point to make another trip, but i would need to count each time it does it. It will over the period make many journies and often the same

Hi Dave, This is great, it almost works, but a journey out is recorded as LOMA and the return is MALO

I will have a play around but i think the pivot maybe the easiest option

Thank you all!