Question: How do i automatically merge two lists on two different sheets but with identical headers?

merge_sheets.png

merge_sheets_1.png

Answer: I used this formula in all of the cells: =IF(IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))=0,"", IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1, ROW()-SUM(IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))) + Ctrl + Shift + Enter

Here is the result:

merge_sheets_2.png

So what is this formula really doing. Let us start with a smaller part of the formula:
OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) retrieves the values on Sheet1.

IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1), OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)) checks if the values on sheet1 are ending and if so starts to get the values on sheet3.

=IF(IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))=0,"", IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1, ROW()-SUM(IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))) checks if the values on sheet3 equals 0 and if so display nothing.

Download excel sample file for this tutorial.
merge-two-sheets.xls
(Excel 97-2003 Workbook *.xls)

SUM(number1,[number2],>)
Adds all the numbers in a range of cells

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
Returns the rownumber of a reference

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference