Search code examples
excelexcel-2010formulaexcel-365

Ammending Equation so that columns A,B do not have to be adjacent to eachother

I have this formula below which sorts data In columns A and B as shown in the image.

enter image description here

The formula is: =SUBSTITUTE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>'",,A2:B60)&"</s></t>","//s"),"'","").

The problem with this question however is that it requires Columns A and B to be adjacent to each other.

Gary Student provided me with the above and an alternative solution that enables the same affect without the columns being adjacent, but this requires a LET function. For those interested this solution is: =LET(x,INDEX(A$2:E$22,ROUNDUP(SEQUENCE(42)/2,0),IF(MOD(SEQUENCE(42),2)=0,5,1)),FILTER(x,x<>"")).

For whatever reason however my work computer despite having excel 365 does not have the LET function and I am unable to update it.

I was wondering if anyone knew how to amend this formula so that columns A and B don't have to be Adjacent.

Solution

  • Say you needed columns A and C:

    =SUBSTITUTE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>'",,CHOOSE({1,2},IF(A2:A60="","",A2:A60),C2:C60))&"</s></t>","//s"),"'","")

    should do it.