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

Question

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

As you haven't specified an Excel version, please see this article https://medium.com/swlh/combining-multiple-tables-into-one-c21aa5bdf36f — Spectral Instance, May 03 '22 at 00:58
@SpectralInstance (or others) How to check Excel version? I have Excel for microsoft 365, which has `XFILTER` but not `VSTACK` or `LET` functions, below answers are referring to "newest" version of excel and office 365, is office 365 different than microsoft 365? — tnavidi, May 04 '22 at 08:38
@tnavidi, if you have 365 then do you have FILTER(no x) and LET - you will only have the others (VSTACK) etc, if you deliberately opted-in to the Insiders program (that option is is displayed on your File-> Account page) — Spectral Instance, May 04 '22 at 11:57
@tnavidi, I just saw your comment about LAMBDA, everyone on 365 should have it, as it went GA last year - if you don't then it's because of policy restrictions in your organisation — Spectral Instance, May 04 '22 at 12:20
@SpectralInstance Thanks, I'm missing the insider button, probably due to policy restrictions as you suggested. — tnavidi, May 04 '22 at 13:18

score 1 · Answer 1 · answered May 03 '22 at 06:09

1

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.

answered May 03 '22 at 06:09

JvdV

70,606
8
39
70

`=VSTACK(TOCOL(Sheet1!A:A,1),TOCOL(Sheet2!A:A,1))` Formula in the `Sheet3 A1` doesn't work – user2535338 May 03 '22 at 11:22
@user2535338, you probably don't have access to these new functions yet. I assume you recieve the `#NAME` error. You'd need ms365's BETA-channel for insiders to use the above. – JvdV May 03 '22 at 11:24
How do I sign up to have access to this function? – user2535338 May 03 '22 at 21:14
@user2535338 see [here](https://insider.office.com/en-us/join/windows) – JvdV May 03 '22 at 21:22
I have to use windows to do that, haven't I? I'm using manjaro linux, I've found any thing in the browser – user2535338 May 03 '22 at 23:45

Spectral Instance · Accepted Answer · 2022-05-04T19:17:19.680

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)
)))))))))

edited May 04 '22 at 19:17

answered May 04 '22 at 01:12

Spectral Instance

1,971
2
3
6

In this case you have to know the cell value, in my case didn't work – user2535338 May 04 '22 at 13:06
@user2535338 please clarify, I don't know what you mean by 'cell value' - you can see from the link that the formula works online ? – Spectral Instance May 04 '22 at 13:22
`one` and `two` are the variables, aren't they? In my excel didn't worked – user2535338 May 04 '22 at 13:37
Yes, they're variables - you would have to adjust them if your tabs aren't called Sheet1 and Sheet2... – Spectral Instance May 04 '22 at 13:41
So I've done it correctly, but my excel didn't recognize the formula – user2535338 May 04 '22 at 13:46
Please post the formula you've used, and clarify what your tab names are (or, better yet, post a link to your actual file) – Spectral Instance May 04 '22 at 13:47
Added the file, I want to append the sheet1 to sheet9 in sheet10, they can vary the in size – user2535338 May 04 '22 at 14:29
Updated my answer based on your 'clarification' – Spectral Instance May 04 '22 at 19:18
I think that my office 365 is not the same. for me here is not appearing anything, just a red rectangle around the cell formula – user2535338 May 04 '22 at 23:50
Excel for the web is not Office 365 and, to my knowledge, is the same for everyone (who doesn't have a business account) - please update the linked file (in your OP) with the formula, and then I will have a look – Spectral Instance May 04 '22 at 23:53
Updated the file. can you access it? – user2535338 May 05 '22 at 00:00
Yes, but I can't edit the file. Can you make a copy of your file, one that is editable for me, then I'll see if I can get that copy working ? – Spectral Instance May 05 '22 at 00:03
https://1drv.ms/x/s!AtPc07fC4Qqyj-Rc6kRVDySsgrdQZA?e=Lgb1WZ try this one – user2535338 May 05 '22 at 00:17
1

Ok, I've updated it - please check – Spectral Instance May 05 '22 at 00:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244516/discussion-between-user2535338-and-spectralinstance). – user2535338 May 05 '22 at 17:27

score 0 · Answer 3 · answered May 03 '22 at 13:14

0

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.

answered May 03 '22 at 13:14

mark fitzpatrick

3,162
2
11
23

`fa` and `fb` is the `sheetA` and `sheetB`? I'm in office 365 – user2535338 May 03 '22 at 18:42
@user2535338 Actually, **fa** and **fb** are calculated variables from **a** and **b**. So, **a** would be `SheetName1!A:A` and **b** would be `SheetName2!A:A`. Let **fa** and **fb** stay as they are - they are the filters of a and b. – mark fitzpatrick May 03 '22 at 20:53
I've used the following equasion `=LET( 'Sheet1'!A:A, A:A, 'sheet2'!A:A, A:A, 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 ) ) )` and it just stayed the same – user2535338 May 03 '22 at 21:13
@user2535338 to use Marks answer, you need to replace the addresses for `a` and `b` but leave the identifiers as they are, ie `=LET( a, 'Sheet1'!A:A, b, 'Sheet2'!A:A, ...)` – chris neilsen May 04 '22 at 01:25

tnavidi · Answer 4 · 2022-05-03T13:56:01.857

0

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)

edited May 03 '22 at 13:56

answered May 03 '22 at 13:50

tnavidi

1,043
6
12

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

4 Answers4

Linked

Related