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]

What version of Excel are you using, as it's obviously not 2013?

If you use

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

with the range you are getting problems with do you still get the error?

 

[Spoursy]

Hi Fluff, Thanks for your reply. Im using 365. If I replace my formula with yours I dont get a spill of data

 

[Fluff]

Im using 365

Then I suggest that you update your profile to show that.

If I replace my formula with yours I dont get a spill of data

But do you get the #VALUE! error when you change the range to whatever is giving problems?

 

[Spoursy]

Thanks - Ive updated my office version in my profile

When I put your formula in, replacing all of mine, I get one cell of data but no #value! error. I need to condense the table into a single column of unique values. Roger

 

[Fluff]

In that case you probably have cells that contain characters filterxml doesn't like. Do you have the new TEXTSPLIT function?

 

[Spoursy]

It would appear not. When I have typed =textsplit excel doesnt seem to recognise it. I do have textJoin

 

[Spoursy]

Am i supposed to just be using the =TEXTJOIN("|",TRUE,'Unique Master List (filtered)'!CV17:DQ110) that you suggested or adding it to my original formula of =SORT(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,'Unique Master List (filtered)'!CV17:DQ110),"|","</b><b>")&"</b></a>","//b")))

 

[Fluff]

What range were you using when you got the error?

 

[Spoursy]

It will work with ranges in size upto: Unique Master List (filtered)'!CV17:DQ110) But if i change the DQ110 to any row greater than that I get the #value! error