0

So I have managed to find and workout how the following line of formula works but whenever I attempt to add onto it I seem to be scratching my head and can't figure out how to get it to work with another four columns. I have tried IFERROR but then it just comes up ass blank after the first column even when there are different values in the second column.

=IF(SEQUENCE(COUNTA(A:A)+COUNTA(C:C))<COUNTA(A:A)+1,INDEX(A:A,SEQUENCE(COUNTA(A:A)+COUNTA(C:C))),INDEX(C:C,SEQUENCE(COUNTA(A:A)+COUNTA(C:C))-COUNTA(A:A)))
vimuth
  • 5,064
  • 33
  • 79
  • 116
Dan
  • 1
  • I take it you don't have VSTACK available? – Rory Jul 15 '22 at 10:24
  • Please check out this Q/A: https://stackoverflow.com/q/71112859/3688861 with some good answers by jvdV https://stackoverflow.com/a/71113545/3688861 – Tragamor Jul 15 '22 at 10:26

1 Answers1

1

If you use this formula instead it is easier to expand:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A:A,C:C)&"</s></t>","//s")

for example to this: (didnt know which columns to use)

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A:A,B:B,C:C,D:D,E:E,F:F)&"</s></t>","//s")

This way you can also, if needed, apply text filters as described in this article: Excel - Extract substring(s) from string using FILTERXML which contains examples for removing duplicates, substituting letters, showing only numerical elements, etc.

tnavidi
  • 1,043
  • 6
  • 12