Using Sort, Unique & Filter to create a single list from a table..

[Spoursy]

Hi, Im using this formula to condense data from a table into a single column of data
=SORT(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,'Unique Master List (filtered)'!CV17:DQ110),"|","</b><b>")&"</b></a>","//b")))

If i change the table range past row 110 i get a #Value! error

Any ideas welcome

 

[Fluff]

In that case if you put this into a cell, what does it return

=TEXTJOIN("|",TRUE,'Unique Master List (filtered)'!CV17:DQ111)

 

[Spoursy]

its coming back with a whole load of data. I looks like it is reading the table from left to right, top to bottom.

 

[Fluff]

Ok, in another cell put

=LEN(A2)

changing the A2 to look at the cell with the textjoin in it & what does it return?

 

[Spoursy]

20446

 

[Fluff]

In that case you are exceeding the limits of the filterxml function.
Do you have the REDUCE function?

 

[Spoursy]

Hi - It looks like I have reduce.

So as not to exceed the limits of filterxml function, I could replace the data thats in the table to a series of numbers instead of strings of text - would that help it?

 

[Fluff]

How about

=LET(Data,'Unique Master List (filtered)'!CV17:DQ111),r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),x,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),SORT(UNIQUE(FILTER(x,x<>""))))

 

[Spoursy]

Thanks - Just pasted that in and the error is coming up as "you've entered too few arguments for this function"

 

[Fluff]

Oops, got an extra bracket in there, it should be

=LET(Data,'Unique Master List (filtered)'!CV17:DQ111,r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),x,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),SORT(UNIQUE(FILTER(x,x<>""))))

 

[Spoursy]

You are a genius - thanks for this.