Stacking two list of data on top of each other in excel

Question

I have two excel sheets with one column each.

Sheet1

A
Fruits
Orange
Apple
Grapes

Sheet2

B
Vegetables
Tomatoes
Potatoes

Now, how do I use excel formula and populate on a third sheet stacking just the values

A
Orange
Apple
Grapes
Tomatoes
Potatoes

Do you have Office 365 (HSTACK or VSTACK does exactly what you describe) — P.b, Oct 19 '22 at 17:15
[Here](https://stackoverflow.com/a/69861437/445425) is a generalised Union function, for those that don't have VStack — chris neilsen, Oct 19 '22 at 22:38

P.b · Answer 1 · 2022-10-19T18:34:01.963

If your data has a header in Sheet1!A1 and Sheet2!A1 then in Sheet3!A1 you could use (and drag down):

=IF(ROW()<=ROWS(Sheet1!$A$2:$A$4),
           Sheet1!A2,
           SUBSTITUTE(
                         INDEX(Sheet2!A:A,
                         ROW()-ROWS(Sheet1!$A$2:$A$4)+1),
                      "",""))

If the row number in the new sheet is smaller than or equal to the count of rows in the range of Sheet1 then the result is Sheet1!A2:A4 if the row is greater it'll index Sheet2 column A and will get the result for in that range with the row that equals to the current row number minus the total count of rows of Sheet1!A2:A4 + 1 to take the header into account.

In Office 365 a simple =HSTACK(Sheet1!A2:A4,Sheet2!A2:A3) would do.

Stacking two list of data on top of each other in excel

1 Answers1