0

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.

Nick
  • 789
  • 5
  • 22
  • Copy the formula starting with `Filter(..` and replace the `x` with the `INDEX(...` part. That is all the LET is doing. – Scott Craner Feb 08 '21 at 15:10
  • Sorry, I dont quite understand what you mean. – Nick Feb 08 '21 at 15:24
  • `=FILTER(INDEX(A$2:E$22,ROUNDUP(SEQUENCE(42)/2,0),IF(MOD(SEQUENCE(42),2)=0,5,1)),INDEX(A$2:E$22,ROUNDUP(SEQUENCE(42)/2,0),IF(MOD(SEQUENCE(42),2)=0,5,1))<>"")` – Scott Craner Feb 08 '21 at 15:29

1 Answers1

1

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.

Rory
  • 32,730
  • 5
  • 32
  • 35