Feb 05 2022 12:32 PM
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
Feb 05 2022 12:37 PM
Feb 05 2022 02:52 PM
SolutionUNIQUE 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.
Apr 06 2022 11:01 AM
Apr 06 2022 02:27 PM
That's for Excel 365, Excel for web or Excel 2021
Apr 06 2022 06:20 PM
Dear Sergei,
Please help me out with any alternative of unique function that can give me unique dates automatically from a list of entries.
Apr 07 2022 02:31 AM
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!
Feb 05 2022 02:52 PM
SolutionUNIQUE 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.