Search code examples
google-sheetsfilteruniquegoogle-sheets-formulagoogle-sheets-query

How to pick one of the results of UNIQUE() in Google Sheets

Problem

I have a column with duplicate items in Google Sheets, and I would like to get one of the unique values (say, the last one) in the cell of the formula. Is there a way to do this with just formulas (i.e., no scripts/macros)?

What I've tried

Not sure if this is the best way, but I've tried using the UNIQUE(range) function, which returns a list of distinct values, and I tried to pick one with FILTER(range, condition1, [condition2, …]), but I've only managed to do it when I know in advance and hard-code in the number of unique values.

enter image description here

Since I can get the length of the unique list with =LEN(UNIQUE(my_range)), I tried using the REPT(text_to_repeat, number_of_repetitions) function.

For example,

=REPT(0&";",2) & 1 returns "0;0;1"

but

=FILTER(UNIQUE(A$1:A$26), {REPT(0&";",2) & 1})

(or any variation I tried) doesn't quite work.

enter image description here

P.S. I realise this is not the most suitable problem for a spreadsheet, and I do wish I was using something like Python, but this is the restriction at the moment.

Solution

  • Or more old-school using index:

    =index(unique(A:A),counta(unique(A:A)))

    enter image description here

    You can also just enter a number fot the one you want e.g.

    =index(unique(A:A),2)