SOLVED

Help on a UNIQUE formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3121396%22%20slang%3D%22fr-FR%22%3EHelp%20on%20a%20UNIQUE%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121396%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20the%20following%20list%20of%20year%3A%3C%2FP%3E%3CP%3E2022%202023%202023%202025%202025%202026%202027%202028%3C%2FP%3E%3CP%3Efrom%20A1%3AK1%3C%2FP%3E%3CP%3EI%20wish%20to%20find%20the%20single%20values%20and%20put%20the%20following%20formula%20in%20B1%3DSINGLE(A1%3AK1)%3C%2FP%3E%3CP%3Ebut%20got%20the%20sames%20values.%3C%2FP%3E%3CP%3EWhat%20I'm%20doing%20wrong%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EBernard%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3121396%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121419%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20a%20UNIQUE%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121419%22%20slang%3D%22en-US%22%3EI%20believe%20you%20want%20the%20unique%20function%3A%3CBR%20%2F%3E%3DUnique(A1%3AK1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121557%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20a%20UNIQUE%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298156%22%20target%3D%22_blank%22%3E%40Bernard_Bouree%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUNIQUE%20works%20down%20the%20array%20to%20generate%20a%20distinct%20set%20of%20rows.%26nbsp%3B%20To%20do%20the%20reverse%20and%20work%20from%20left%20to%20right%20across%20an%20array%20(here%20just%20a%20single%20row)%20you%20need%20to%20add%20a%20further%20parameter%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20UNIQUE(year%2C1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20'%3CSTRONG%3Eyear%3C%2FSTRONG%3E'%20is%20a%20defined%20name%20that%20refers%20to%20%3CSTRONG%3EA1%3AK1.%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121911%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20a%20UNIQUE%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121911%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%3C%2FP%3E%3CP%3EBernard%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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
I believe you want the unique function:
=Unique(A1:K1)
best response confirmed by Hans Vogelaar (MVP)
Solution

@Bernard_Bouree 

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.

Hi @Peter Bartholomew 

Thank you very much

Bernard

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

@Sanjdasq 

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

@Sergei Baklan 

 

Dear Sergei,

 

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

@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!