Filterxml returns Value error

[sanket_sk]

Hi There,

I am using the below formula to extract unique values from multiple columns to a single column.

This works great in the sheet, however, whenever there is an “&” character this formula fails and shows a “Value” error, could you please help me on this.

Book2
	A	B	C	D	E
1	Mumbai				Formula
2		Pune			Indor
3			Indor		Mumbai
4					Pune
5
6	MP & CG				Formula
7		Kerla			#VALUE!
8			UP
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:C4)&"</s></t>","//s")))
E7	E7	=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A6:C11)&"</s></t>","//s")))
Dynamic array formulas.

Thanks & Regards,
Sanket

 

[Fluff]

How about

=SORT(UNIQUE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("</s><s>",TRUE,A6:C11),"&","&amp;")&"</s></t>","//s")))

 

[Peter_SSs]

If you happen to have the new TOCOL function (not everybody has yet) ..

22 05 19.xlsm
	A	B	C	D	E
1	Mumbai				Formula
2		Pune			Indor
3			Indor		Mumbai
4					Pune
5
6	MP & CG				Formula
7		Kerla			Kerla
8			UP		MP & CG
9					UP
10
TOCOL

Cell Formulas
Range	Formula
E2:E4,E7:E9	E2	=SORT(UNIQUE(TOCOL(A1:C4,1)))
Dynamic array formulas.

 

[sanket_sk]

How about

=SORT(UNIQUE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("</s><s>",TRUE,A6:C11),"&","&amp;")&"</s></t>","//s")))

This solution worked well - Thanks for quick fix

 

[sanket_sk]

If you happen to have the new TOCOL function (not everybody has yet) ..

22 05 19.xlsm
	A	B	C	D	E
1	Mumbai				Formula
2		Pune			Indor
3			Indor		Mumbai
4					Pune
5
6	MP & CG				Formula
7		Kerla			Kerla
8			UP		MP & CG
9					UP
10
TOCOL

Cell Formulas
Range	Formula
E2:E4,E7:E9	E2	=SORT(UNIQUE(TOCOL(A1:C4,1)))
Dynamic array formulas.

Hi Peter,

Thanks for the guidence however TOCOL is not available in my system, I will definitely try it once available.


Sanket

 

[Fluff]

Glad we could help & thanks for the feedback.