How does this work? SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))

SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))

Select allOpen in new window

Where:
=SMALL(array, k)
I mean: the help syntax bears barely any relation with the solution above.

SHORT VERSION: Why does that work where UNIQUE() does not?


School's in.

So far I understand that small() picks only one example of each item in the list.

I can see that (A6#,C10:C18) concatenates two arrays, not just one, but it creates one out of two. I can also see that when I get my Value! error it is because I can reproduce the error thus:
Where B6:B7 are in Sheet1!
=B6:B7:Sheet2!B9:B10.        ==Value!
and also
=B6:B7:Sheet1!J6:J7            == Value! So even though in the same sheet, the Sheet1! reference generates the error.
=B6:B7:J6:J7                        ==0 No error.
So we just have to know this.

k is represented by: 

SEQUENCE(ROWS(A6#)+ROWS(C10:C18))

Select allOpen in new window

where:
=SEQUENCE(rows,[columns],[start],[step])
In this case only the rows argument is used.
This generates a series of numbers starting from 1 and running to the number of rows (and thus items) in the concatenated range A6#and C10:C18 (both same/current sheet) (51 in thee example)

So k is not one digit.. but it is; it is 51.

And the penny drops!! kinda.

Where we have k being the same as the number of items in range, SMALL() is looking for .. what?
Help seems confusing because it says k returns the k-th smallest value. That is ONE VALUE only.
Then it says "...return values...." which is what is being done in the above. So SMALL is returning not just one value, but every value smaller or equal to  the largest value in the range, and it returns only one example of each, no point in returning duplicates as that is not answering the question.

How is that different from UNIQUE() ?

Example: LDATELISTSCOMBINE_SchoolEE.xlsx

This is like being on one of those never ending treadmills.. exercise for my TV watching brain.

Anthony












Notes for self:

Description

Returns the k-th smallest value in a data set.
Use this function to return values with a particular relative standing in a data set.

Syntax

SMALL(array, k)
The SMALL function syntax has the following arguments:

• Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.
• K    Required. The position (from the smallest) in the array or range of data to return.