Want to return a unique list of values from a column that contains an array formula

[freeb1893]

I have a column where I'm doing some indexing / matching via an array formula

I'd like to, in another column, calculate a list of unique values from that resulting array formula column

The UNIQUE function doesn't seem to work since the column I'm trying to process the unique values from has that array formula working in it. I keep getting a #SPILL! error.

Any ideas how I can get that unique list of values?

 

[Fluff]

You could also replace you formula with this

Fluff.xlsm
	M	N	O
1
2	AT-33	28/04/2023 10:15	Smith, John
3	EDGE-13283
4	EDGE-13293
5	EDGE-13645
6	EDGE-13900
7	EDGE-13901
8	EDGE-13904
9	EDGE-13905
10	EDGE-13906
11	EDGE-13907
12	EDGE-13908
13	EDGE-13909	29/06/2022 10:33	Gibbs, Harry
14	EDGE-13910
15	EDGE-13911
16	EDGE-13952
Sheet2

Cell Formulas
Range	Formula
M2:M17	M2	=UNIQUE('Raw Data'!$B$2:$B$10000)
N2:O2,N13:O13,N3:N12,N14:N16	N2	=LET(d,'Raw Data'!$M$2:$M$10000,s,SEQUENCE(ROWS(d),,2),r,FILTER(ROW(d),('Raw Data'!$B$2:$B$10000=M2)*(d="development")),o,FILTER(CHOOSECOLS('Raw Data'!$J$2:$M$10000,1,-1),(s=r-1)+(s=r+1),""),IF(COUNT(r),FILTER(o,ISNUMBER(FIND(",",TAKE(o,,-1)))),""))
Dynamic array formulas.

 

[freeb1893]

That means you have cells below the formula that are not completely empty. You need to clear them.
Also with 365 you no longer need to array enter any formula.

Ahh perfect! Thank you so much for your patience! I haven't mucked with Excel for years. That was what I was doing wrong. It works! Thanks again!

 

[Fluff]

You;re welcome & thanks for the feedback.

 

[freeb1893]

As col M on your raw data doesn't have anything saying "Development" it's difficult to help, but if your array formula is in col P try

=unique(filter(p2:p100,p2:p100<>""))

Is there a way to modify this formula where not only does it index unique values, but also sorts them in alphabetical order?

 

[Fluff]

Yup, just wrap it in the SORT function.