Forum Discussion
Stack two columns to one with out VBA
I have 2 columns as above, column A and column B. I want them conbimed to column D without ANY manual work.
Note:
(1) VBA is NOT allowed (by my company policy lol).
(2) You might want to create a reference like C1:C1000 = A1:A1000, C1001:C2000 = B1:B1000, then FILTER C:C. Such solutions is not considered because it uses too much resource but spill when either column A/B has more than 1000 values.
(3) The length of column A & B can be different every time.
(4) Format does not matter.
I've found below solution but wondering if there is any easier way to do that.
Column D & F counts Column A & B.
Column F is SEQUENCE(2+3).
Column G and H convert Column F to row/column address use IF.
Finally column J is generated by the formula in above picture.
As variant
with
=LET( aN, COUNTA(A:A), bN, COUNTA(B:B), k, SEQUENCE(aN+bN), IF(k<=aN, INDEX(A:A,k), INDEX(B:B, k-aN)) )
