CODEWITHSUNDEEP
excelexcel-formula
user2804374
user2804374

Reputation: 23

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

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

Upvotes: 0

Views: 474

Answers (1)

P.b
P.b

Reputation: 11588

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.

Upvotes: 1

Related Questions