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]

This formula works, I extended the the formulas down to row 50,000...because this data will be added to on a monthly basis. so where your example stated $G$2:$G$200, I increased to $G$2:$G$500000 for example.

However, I made my data set a table, so when I add to the bottom it adds to the table. When I swap out the column range to the table headers in the formula, it doesn't work??? I get the "No Data" you set in the formula....?

I really appreciate the formula you gave me and it works, I just don't understand why it doesn't work when referencing the table headers rather than the column reference?

 

[Fluff]

What is the formula with the structured references?

 

[Beau the dog]

=UNIQUE(FILTER(Table3[[#Headers],[Extra Info VRM]],(Table3[[#Headers],[Serco Site]]=Ref!C53)*(LEN(Table3[[#Headers],[Extra Info VRM]])=8)*(ISNUMBER(MID(Table3[[#Headers],[Extra Info VRM]],3,2)+0)),"No data"))

Extra Info VRM is the registration column (Column G)
Serco Site is the contract Column (Column D)

I should probably add, I'm writing this formula on a separate tab, separate tab is called "Ref". I write your first formula on Ref!C52

 

[Fluff]

Ok, get rid of the #Header part. It should be like

Table3[Extra Info VRM]

Also you should never refer to the name of the sheet the formula is on so use

UNIQUE(FILTER(Table3[Extra Info VRM],(Table3[Serco Site]=C53)

 

[Beau the dog]

Ok, get rid of the #Header part. It should be like

Table3[Extra Info VRM]

Also you should never refer to the name of the sheet the formula is on so use

UNIQUE(FILTER(Table3[Extra Info VRM],(Table3[Serco Site]=C53)

You sir, are a bloody legend!

One last problem, again this now works, but when I drag it to the right, the table headers move to the right also. How do I lock the table headers like I would with a usual formula with $

 

[Fluff]

Had a feeling you might ask that.

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

 

[Beau the dog]

Had a feeling you might ask that.

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

Thank you, really appreciate your time.

Have a great bank holiday weekend!!

 

[Fluff]

And you.

 

[Beau the dog]

Good morning,

I've realised this formula is not pulling through all the registrations and I'm trying to understand why!

For some reason, it's not pulling through all the registrations that meet the criteria in your formula, do you have any ideas why?!

for example, LK15BBJ in the table I posted on page one, is one of the registrations not coming through?

 

[Fluff]

Does it have a space before the BBJ?