Originally Posted by
Gregb11
Another option if you keep all the columns on different sheets is to use this formula:
=LET(
a, CHOOSE({1,2},Sheet1!A1:A20,Sheet2!A1:A20),
b, SEQUENCE(ROWS(a)*COLUMNS(a),,0),
c, FILTER(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1),NOT(ISERROR(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1)))),
FILTER(c,c<>0))
The only things you need to change on this formula is after the "Choose", within the brackets, enter in sequential numbers starting with 1 to the number of columns you want to combine, then right after that, list the range of the columns (Sheet1!A1:A20, etc.)
Bookmarks