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

jack109

Board Regular
Joined
May 10, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Hi

Im using this formula to return the unique values from a range:
Excel Formula:
=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

Excel Formula:
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hello, maybe something like

Excel Formula:
=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...
 
Upvote 0
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
Excel Formula:
=LET(d,'CS-LA'!C2:C10000,UPPER(SORT(UNIQUE(FILTER(d,d<>"")))))
 
Upvote 0
Solution
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
CDE
1
2AAAAAA
3AACCCC
4CC
5CC
6CC
7
1f
Cell Formulas
RangeFormula
D2:D3D2=LET(d,C2:C10000,SORT(UNIQUE(FILTER(d,d<>""))))
E2:E3E2=SORT(UNIQUE(TRIMRANGE(C2:C10000)))
Dynamic array formulas.
 
Last edited:
Upvote 0
"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.
 
Upvote 0
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
HI
1a
2ab
3bc
4c0
50
6f
7fg
8g0
9i
10ij
11j
12
13
14
15
Sample
Cell Formulas
RangeFormula
I1:I10I1=TRIMRANGE(H1:H15)
Dynamic array formulas.
 
Upvote 0
You're welcome. Glad we could help. :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top