Slightly complicated Unique list

[Beau the dog]

Good afternoon

I have a table of data, over 25000 rows long. I need to extract a unique list of vehicle registrations per contract. the column which contains the vehicle registrations doesn't just have registrations in, sometimes it says workshop, consumables etc.

How do I extract the unique list, but only return the registrations, example snapshot below. I have put dummy registrations in, but they all follow the vehicle registration format (AB12 ABC).

For reference, Contracts are in column D and registrations and other stuff is in column G.

As always your help is very much appreciated

 

[Beau the dog]

Does it have a space before the BBJ?

No, its just 7 characters in a row. However, I thought that's why you had the LEN formula equalling 8 to account for that? Some registrations do have a space between the first four characters and the last three, but most don't.

I used find and replace to clear the space, but than the formula found only a handful of registrations for each contract

 

[Fluff]

If you've stripped out all the spaces then change LEN=8 to LEN=7

 

[Beau the dog]

The current formula does return both registration formats, this is an example part of the result...

BK05 ORY

KU09 CLL

FJ09 HWU

VO15KYC

VU15JTZ

BV09GLF

 

[Beau the dog]

If you've stripped out all the spaces then change LEN=8 to LEN=7

I've just tried this and it returns hundreds of vehicles, that I'm pretty sure not all of them belong to the respective contracts...

Milton Keynes returns 351 vehicles!

 

[Fluff]

It should only be returning vehicles where col D matches the value in row 1

 

[Beau the dog]

It should only be returning vehicles where col D matches the value in row 1

I wish it was! This is the formula as it stands...

=UNIQUE(FILTER(Table3[[Extra Info VRM]:[Extra Info VRM]],(Table3[[Serco Site]:[Serco Site]]=G53)*(LEN(Table3[[Extra Info VRM]:[Extra Info VRM]])=7)*(ISNUMBER(MID(Table3[[Extra Info VRM]:[Extra Info VRM]],3,2)+0)),"No data"))

G53 is the contract name

 

[Fluff]

If you have Milton Keynes in G53 then it should only return values from Extra Info VRM where Serco Site has Milton keynes.

 

[Beau the dog]

If you have Milton Keynes in G53 then it should only return values from Extra Info VRM where Serco Site has Milton keynes.

Yeah it does say Milton Keynes

I've just dip checked around 100 of the 350 vehicles and they all belong to Milton Keynes...they must be including their hire vehicles in the orders. There is no way they've had that number of core fleet, but possibly with hire vehicles added as well.

Well at least the formula is working. Thanks for your time again!

 

[Fluff]

You're welcome & thanks for the feedback.