'Excel, append one full range to the end of another in one full column
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?
EDITED: I'm having a problem with Excel 365, I'm using Manjaro Linux, so I'm working in the chrome browser. The problem is that I have one excel file with 10 sheets in it. I want to get columns A B C and D from 9 sheets and append them in one big table on sheet 10.
Sheet1:
Tarefa Atividade Importante Urgente Numero-Total-de-Tarefas
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
Sheet2:
Tarefa Atividade Importante Urgente Numero-Total-de-Tarefas
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
And the same thing for the other 7 tables.
I want to get all these 9 tables and append them into the sheet10
like:
Sheet10
Tarefa Atividade Importante Urgente Numero-Total-de-Tarefas
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
A 1 asd fg gh
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
B 1 asd fg gh
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
C 1 asd fg gh
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
D ... ... ... ...
D ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
The file Link
Edited:
I guess it's my office with the @SpectralInstance formula:
it's getting a result like this:
That's the only explanation
Thanks a lot, guys
Solution 1:[1]
The formula below (adapted from the link I posted in my comment)
=LET(one,Sheet1!A1:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)),two,Sheet2!A1:INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A)),IFERROR(INDEX(one,SEQUENCE(ROWS(one)+ROWS(two))),INDEX(two,SEQUENCE(ROWS(one)+ROWS(two))-ROWS(one))))
will work in Excel online, as illustrated in this (read-only) file.
EDIT 4-May-2022
Based on your updated, and dramatically different, specification, this formula, as well as filling half the screen, will take all data from Sheet1, including the headers, and the header-exclusive data from Sheets 2 through 9, and consolidate them:
=LET(t_1,Sheet1!A1:INDEX(Sheet1!F:F,COUNTA(Sheet1!E:E)),t_2,Sheet2!A2:INDEX(Sheet2!F:F,COUNTA(Sheet2!E:E)),
t_3,Sheet3!A2:INDEX(Sheet3!F:F,COUNTA(Sheet3!E:E)),t_4,Sheet4!A2:INDEX(Sheet4!F:F,COUNTA(Sheet4!E:E)),
t_5,Sheet5!A2:INDEX(Sheet5!F:F,COUNTA(Sheet5!E:E)),t_6,Sheet6!A2:INDEX(Sheet6!F:F,COUNTA(Sheet6!E:E)),
t_7,Sheet7!A2:INDEX(Sheet7!F:F,COUNTA(Sheet7!E:E)),t_8,Sheet8!A2:INDEX(Sheet8!F:F,COUNTA(Sheet8!E:E)),
t_9,Sheet9!A2:INDEX(Sheet9!F:F,COUNTA(Sheet9!E:E)),width,SEQUENCE(,6),
IFERROR(INDEX(t_1,SEQUENCE(ROWS(t_1)),width),
IFERROR(INDEX(t_2,SEQUENCE(ROWS(t_1)+ROWS(t_2))-ROWS(t_1),width),
IFERROR(INDEX(t_3,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3))-ROWS(t_1)-ROWS(t_2),width),
IFERROR(INDEX(t_4,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3),width),
IFERROR(INDEX(t_5,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4)+ROWS(t_5))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3)-ROWS(t_4),width),
IFERROR(INDEX(t_6,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4)+ROWS(t_5)+ROWS(t_6))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3)-ROWS(t_4)-ROWS(t_5),width),
IFERROR(INDEX(t_7,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4)+ROWS(t_5)+ROWS(t_6)+ROWS(t_7))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3)-ROWS(t_4)-ROWS(t_5)-ROWS(t_6),width),
IFERROR(INDEX(t_8,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4)+ROWS(t_5)+ROWS(t_6)+ROWS(t_7)+ROWS(t_8))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3)-ROWS(t_4)-ROWS(t_5)-ROWS(t_6)-ROWS(t_7),width),
INDEX(t_9,SEQUENCE(ROWS(t_1)+ROWS(t_2)+ROWS(t_3)+ROWS(t_4)+ROWS(t_5)+ROWS(t_6)+ROWS(t_7)+ROWS(t_8)+ROWS(t_9))-ROWS(t_1)-ROWS(t_2)-ROWS(t_3)-ROWS(t_4)-ROWS(t_5)-ROWS(t_6)-ROWS(t_7)-ROWS(t_8),width)
)))))))))
Solution 2:[2]
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.
Solution 3:[3]
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.
Solution 4:[4]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | JvdV |
| Solution 3 | mark fitzpatrick |
| Solution 4 |