Jun 03 2021 07:10 AM
hello Excel helpers,
I am trying to get FILTERXML return multiple rows and columns with one call.
my xml example is :
<y><x><a>1.445</a><b>1.445</b></x><x><a>3.443</a><b>1.445</b></x><x><a>-1.443</a><b>10.445</b></x><x><a>3</a><b>1.445</b></x></y>
my xpath is :
//x/a | //x/b
Excel now returns 1 column with 8 rows, whilst I would like to get 2 columns, each having 4 rows ( 1 column for the a field, and one column for the b field )
Is this possible. Note that I cannot create two distinct FILTERXML functions as I need to have the resulting dynamic array covering the complete dataset.
thnaks for your advise
guido
Jun 03 2021 07:36 AM
Solution@gdebouvertrinseocom there might be a more elegant solution, but this will work:
=LET(xml,"<y><x><a>1.445</a><b>1.445</b></x><x><a>3.443</a><b>1.445</b></x><x><a>-1.443</a><b>10.445</b></x><x><a>3</a><b>1.445</b></x></y>",x,"//x/a",y,"//x/b",CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y)))
Jun 03 2021 07:48 AM
Jun 03 2021 07:36 AM
Solution@gdebouvertrinseocom there might be a more elegant solution, but this will work:
=LET(xml,"<y><x><a>1.445</a><b>1.445</b></x><x><a>3.443</a><b>1.445</b></x><x><a>-1.443</a><b>10.445</b></x><x><a>3</a><b>1.445</b></x></y>",x,"//x/a",y,"//x/b",CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y)))