SOLVED

FILTERXML to return multiple rows and columns

New Contributor

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

2 Replies
best response confirmed by gdebouvertrinseocom (New Contributor)
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)))
unbelievable....
=CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y))
does the job.

Thanks a lot - you saved my day - Excel is such as a great tool