0

I have two vertical dynamic arrays (A2#, C2#) that include the cell mentioned below:

A2#

Cell A2
Cell A3
Cell A4
..

C2#

Cell C2
Cell C3
..

I want to have a formula that concatenates them into one column vertically so that A2# elements come first and then C2# elements come after

Combining A2# and B2# vertically will have the cell content of A2, A3, A4.. then C2, C3.. into an array say E1#, as in below

          E
   -------------
1  content of A2
2  content of A3
3  content of A4
.. ..
7  content of C2
8  content of C3
9  ..

Please help

Swift Dev Journal
  • 19,282
  • 4
  • 56
  • 66
Ahed
  • 38
  • 4
  • Can you share your attempt on solving your own issue for debugging? Otherwise this question is off-topic – JvdV Jan 27 '22 at 23:01
  • [See this answer](https://stackoverflow.com/a/69861437) – chris neilsen Jan 27 '22 at 23:16
  • thanks, @chrisneilsen, your solution is great, unfortunately, my excel (of office 360) does not support LAMBDA, I used the JvdV solution though, thanks to both of you – Ahed Jan 28 '22 at 00:29
  • @JvdV I used your solution and removed the duplicate clearance. '=LET(X, CHOOSE({1,2}, A2#, C2#), Y, COUNTA(X), Z, MOD(SEQUENCE(Y)-1,Y/2) + 1, A, INDEX(X, Z, CEILING(SEQUENCE(Y)/(Y/2), 1)), A)'. Sorry for not being clear in my question – Ahed Jan 28 '22 at 00:37

0 Answers0