Unique, Filter and Sort Using a Range defined by two cells

[jack109]

Hi

Im using this formula to return the unique values from a range:

=UPPER(SORT(UNIQUE(FILTER('CS-LA'!C:C,'CS-LA'!C:C<>"")),,1))

How do I change it, so that instead of using the whole column, it will just use a defined range set between two cells? I want to this is so the range expands as more data is added. I don't want to use C:C as its pulling the heading from the column in question and I don't want the heading in the unique values.

So A1 will set the range and A2 will be end the range. I've tried adding

INDEX(CS-LA'!C:C,$A$1):INDEX(CS-LA'!C:C,$A$2

but that is just returning the first value and not all the unique values like the original formula.

Thanks

 

[hagia_sofia]

Hello, maybe something like

=DROP(TOCOL(A:A,1),1)

provided that A1 is the header. If it is so, you could then wrap it into UNIQUE and SORT. Or you could transform the data into an official Excel Table...

 

[Peter_SSs]

I think that the DROP(TOCOL approach above would be the way to go, but another option would be to exclude C1 and specify a range that is sure to be big enough to cover any amount of data you are likely to have. For example

=LET(d,'CS-LA'!C2:C10000,UPPER(SORT(UNIQUE(FILTER(d,d<>"")))))

 

[Dave Patton]

N.B. I am not questioning Peter's suggestion in #3.

There is a new function named Trimrange that could be considered.
Does anyone have experience use TrimRange?

T202411a.xlsm
	C	D	E
1
2	AA	AA	AA
3	AA	CC	CC
4	CC
5	CC
6	CC
7
1f

Cell Formulas
Range	Formula
D2:D3	D2	=LET(d,C2:C10000,SORT(UNIQUE(FILTER(d,d<>""))))
E2:E3	E2	=SORT(UNIQUE(TRIMRANGE(C2:C10000)))
Dynamic array formulas.

 

[hagia_sofia]

@Dave Patton : has TRIMRANGE already been released or is still available only to insiders?

 

[Dave Patton]

"has TRIMRANGE already been released or is still available only to insiders?"

I do not know. I am not an insider but I do use the Beta version.

I tried to edit my post several times but the internet did not cooperate.

TrimRange is included in the Beta Channel; I do not know what channels or releases of Excel have it.

 

[Peter_SSs]

We do not really know what the OP's data is like but TRIMRANGE may not be suitable here because it does not deal with this requirement 'CS-LA'!C:C<>"" unless the blank cells are only at the beginning or end of the range. That is, it would not eliminate blanks amongst the data.
Example:
Here you would still have to filter to eliminate the internal blanks so you might as well just filter blanks to start with.
(I'm an Insider on Beta Channel)

24 11 03.xlsm
	H	I
1		a
2	a	b
3	b	c
4	c	0
5		0
6		f
7	f	g
8	g	0
9		i
10	i	j
11	j
12
13
14
15
Sample

Cell Formulas
Range	Formula
I1:I10	I1	=TRIMRANGE(H1:H15)
Dynamic array formulas.