Search code examples
google-sheetsfilteruniquegoogle-sheets-formula

Use Unique in a Filter formula

I have a list of identifiers and a value for each identifier which is filled automatically.
I want to filter the list so I will only get unique identifiers and their respective value.
e.g. "Dana" can appears 3 times but in the filtered table I only want to see the name (and the value) once.
Ideally I'd like to use something like =filter(a:b,unique(a:a) which obviously doesn't work.

As mentioned, the list updates automatically so a formula that needs to be dragged won't do the trick.

Note: It can be solved by extracting uniques from col A

=unique(A:A)

and then an Arrayformula + vLookup

=arrayformula(if(I1:I>0,vlookup(I1:I,A:B,2,0),""))

but I'm curious to see if it can be solved using Filter for more elegance.

Here's an example (including the solution I mentioned): https://docs.google.com/spreadsheets/d/1heKdV3U6mdGYkHCIWkeUyqo6AfhgV7ItSmolibH7ecU/edit?usp=sharing

Solution

  • Please use the following

    =UNIQUE(A:B)

    enter image description here

    UPDATE

    Following OP's comment/request:

    Nice fix! Out of curiosity - is it possible to still use it with the filter function (for example, if I wanted to filter by Col B or add other restrictions) ?

    Sure. Try these ones out

    =UNIQUE(FILTER(A:B,B:B=333))

    OR

    =FILTER(UNIQUE(A:B),UNIQUE(B:B)=333)

    Reference: