SOLVED

Help on a UNIQUE formula

Occasional Visitor

Help on a UNIQUE formula

Hi

I have the following list of year:

2022 2023 2023 2025 2025 2026 2027 2028

from A1:K1

I wish to find the single values and put the following formula in B1=SINGLE(A1:K1)

but got the sames values.

What I'm doing wrong ?

Thanks

Bernard

7 Replies

Re: Help on a UNIQUE formula

I believe you want the unique function:
=Unique(A1:K1)
best response confirmed by Hans Vogelaar (MVP)
Solution

Re: Help on a UNIQUE formula

UNIQUE works down the array to generate a distinct set of rows.  To do the reverse and work from left to right across an array (here just a single row) you need to add a further parameter

= UNIQUE(year,1)

where 'year' is a defined name that refers to A1:K1.

Re: Help on a UNIQUE formula

Thank you very much

Bernard

Re: Help on a UNIQUE formula

I am not getting the option to use unique function in professional 2019. Please help

Re: Help on a UNIQUE formula

That's for Excel 365, Excel for web or Excel 2021

Re: Help on a UNIQUE formula

Dear Sergei,

Please help me out with any alternative of unique function that can give me unique dates automatically from a list of entries.

Re: Help on a UNIQUE formula

@Sanjdasq

I am not the best person to ask because this time I deliberately erased older versions of Excel and decided that I had no interest in maintaining compatibility with those older versions.  However, I remember tricks such as using MATCH to identify the first occurrence of the value (in your case, a date) and return the relative row number of such matches.  SMALL will then filter the row numbers which are ultimately used to lookup the values/dates.

``````= INDEX(
dates,
SMALL(
IF(
MATCH(dates,dates,0)=k,
k),
k)
)``````

I can't help but feel that

``= UNIQUE(dates)``

is clearer in its intent!