SOLVED

FILTERXML to return multiple rows and columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2412518%22%20slang%3D%22en-US%22%3EFILTERXML%20to%20return%20multiple%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412518%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%20Excel%20helpers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20FILTERXML%20return%20multiple%20rows%20and%20columns%20with%20one%20call.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20xml%20example%20is%20%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CY%3E%3CX%3E%3CA%3E1.445%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E3.443%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E-1.443%3C%2FA%3E%3CB%3E10.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E3%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3C%2FY%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20xpath%20is%20%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%2F%2Fx%2Fa%20%7C%20%2F%2Fx%2Fb%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20now%20returns%201%20column%20with%208%20rows%2C%20whilst%20I%20would%20like%20to%20get%202%20columns%2C%20each%20having%204%20rows%20(%201%20column%20for%20the%20a%20field%2C%20and%20one%20column%20for%20the%20b%20field%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible.%20Note%20that%20I%20cannot%20create%20two%20distinct%20FILTERXML%20functions%20as%20I%20need%20to%20have%20the%20resulting%20dynamic%20array%20covering%20the%20complete%20dataset.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethnaks%20for%20your%20advise%3C%2FP%3E%3CP%3Eguido%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412518%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2412594%22%20slang%3D%22en-US%22%3ERe%3A%20FILTERXML%20to%20return%20multiple%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668823%22%20target%3D%22_blank%22%3E%40gdebouvertrinseocom%3C%2FA%3E%26nbsp%3B%20there%20might%20be%20a%20more%20elegant%20solution%2C%20but%20this%20will%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(xml%2C%22%3CY%3E%3CX%3E%3CA%3E1.445%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E3.443%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E-1.443%3C%2FA%3E%3CB%3E10.445%3C%2FB%3E%3C%2FX%3E%3CX%3E%3CA%3E3%3C%2FA%3E%3CB%3E1.445%3C%2FB%3E%3C%2FX%3E%3C%2FY%3E%22%2Cx%2C%22%2F%2Fx%2Fa%22%2Cy%2C%22%2F%2Fx%2Fb%22%2CCHOOSE(%7B1%2C2%7D%2CFILTERXML(xml%2Cx)%2CFILTERXML(xml%2Cy)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2412623%22%20slang%3D%22en-US%22%3ERe%3A%20FILTERXML%20to%20return%20multiple%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412623%22%20slang%3D%22en-US%22%3Eunbelievable....%3CBR%20%2F%3E%3DCHOOSE(%7B1%2C2%7D%2CFILTERXML(xml%2Cx)%2CFILTERXML(xml%2Cy))%3CBR%20%2F%3Edoes%20the%20job.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot%20-%20you%20saved%20my%20day%20-%20Excel%20is%20such%20as%20a%20great%20tool%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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