Excel, append one full range to the end of another in one full column-CodePudding

I have two columns of data in Excel in different sheets. I would like to add a third column in another sheet, which combines the first and second. How can I do this with a formula such that I can add or remove data from columns A in the sheet 1 and B in the sheet 2 without ever having to touch column C in the sheet 3?

I would like to copy the whole column for example:

=append(SheetName1!A:A ,SheetName2!A:A)

Is that possible?

Thanks a lot, guys

CodePudding user response：

Yes it's possible with the newest Excel functions:

Formula in F1:

=VSTACK(TOCOL(A:A,1),TOCOL(D:D,1))

You can now remove/add values in either column to your liking without having to adjust the formula in F1.

CodePudding user response：

It would be helpful to know your version of Excel. So if you don't have access to Early Adopters, but you are Office 365, you can do:

=LET( a, A:A, b, B:B,
        fa, FILTER( a, NOT(ISBLANK(a))), fb, FILTER( b, NOT(ISBLANK(b))),
        ra, ROWS(fa), rb, ROWS(fb),
        rSeq, SEQUENCE(ra   rb),
        IFERROR( INDEX( fa, rSeq), INDEX( fb, rSeq - ra )  ) )

where a and b are the columns you want to append.

CodePudding user response：

you can use this formula, I've assumed the two columns are A and B:

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

The FilterXML opens for other functionality as well, which you can explore here: Excel - Extract substring(s) from string using FILTERXML

(My excel doesn't have the lambda functionality as described in some of the other answers)