Combine column cell interchangeably in one column

Question

Let us assume we have the following tow columns :

column 1

1
3
5

column 2

2
4
6

the result column

and in case the two columns are not equal in size, the combined result should be without empty cells at the end!

player0 · Accepted Answer · 2020-05-05T08:40:20.970

4

=FLATTEN(A1:B)

=TRANSPOSE(SPLIT(TEXTJOIN("♦", 1, A1:B), "♦"))

=INDEX(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A1:B="",,"♦"&A1:B)),,99^99)),,99^99), "♦")))

edited May 05 '20 at 08:40

answered May 05 '20 at 08:14

player0

Any documentation or source for flatten? – TheMaster May 05 '20 at 15:48
@TheMaster nope, its a hidden function – player0 May 05 '20 at 16:26
How did you figure it out? – TheMaster May 05 '20 at 18:17
2

@TheMaster first time heard it few months ago from MattKing and 2 days ago he shared a story about it in comments of https://stackoverflow.com/a/61566901/5632629 – player0 May 05 '20 at 18:24
Thanks for sharing. – TheMaster May 05 '20 at 18:30

1 Answers1