How do I merge two tables in Excel that have identical columns?

I found a way of doing it. This solution is a little tricky and it requires both tables to have their own separate sheets (with nothing else on them), but other than that it does almost exactly what I want. (There also seems to be a lot of potential here for doing more complex operations such as joins.)

Go to the data tab on the ribbon, click "From Other Sources", and "From Microsoft Query". Then click Excel Files, select the file that you are currently working in and click okay. Then, hit cancel and when promoted on whether you want to continue editing in Microsoft Query, hit "Yes". From here you can click the SQL button and write a custom SQL Query on any sheet in the spreadsheet. In my case:

SELECT *
FROM `'Sheet1$'` `'Sheet1$'`
UNION ALL
SELECT *
FROM `'Sheet2$'` `'Sheet2$'`

Note: For me, this method stops working after I close the file and the open it again. I'm posting it here anyway though in case it's just a problem with my computer or someone else can get it to work.

If you are interested in a VBA solution, I was able to get the following to work:

• Set a dynamic named range for the data you are pulling in from SQL Server. Open the Name Manager, enter a new name, (say, "SQLDB") and and copy the following formula in the Refers To input box. I have assumed that your pulled-in data is in Sheet1:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
  

• Set another named range for the range in which the manual data is entered. I used the name EXCELRNG and assumed it was in Sheet2. The named range starts in row 2 to exclude a header row. The formula here is identical to the first one except for the sheet it refers to:

  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1))
  

• Here are the first set of settings I used for the connection to the SQL table. The dialog box is accessed by selecting Connections in the Data tab on the Ribbon. Disabling background refreshing ensures that the VBA macro pauses until a refresh of the data in the Excel sheet is complete. Having the connection refresh when the worksheet opens may not be needed, but I wanted to make sure that any authentication would be done before the macro was run.
  
  

• Here are the second set of settings. These are found in the Properties section of the Data tab (while a cell in the imported SQL table is selected). Although I chose the "Insert entire rows for new data, delete unused cells," option, I actually didn't run into any trouble with the "Insert cells..." option.
  
  

• Finally, this is the VBA code. To insert it, choose Visual Basic under the Developer tab. Highlight the worksheet name in the list at the left. It will be referenced as "VBA Project(sheet name). Then choose Insert Module in the menu bar at the top of the screen, and paste the code in the new module. Note that I put the consolidated table in Sheet3. As written, the macro does not sort the new table, though that would not be hard to add.

  Sub StackTables()
  
     Dim Rng1 As Range, Rng2 As Range
  
     Set Rng1 = ThisWorkbook.Names("SQLDB").RefersToRange
     Set Rng2 = ThisWorkbook.Names("EXCELRNG").RefersToRange
  
     ' refresh the SQL table
     ThisWorkbook.Connections(1).Refresh
  
     ' clear the consolidated table range  
     Sheet3.Cells.ClearContents
  
     ' copy the SQL data into the consolidation range
     Rng1.Copy
     Sheet3.Range("A1").PasteSpecial xlPasteValues
  
     'copy the manually entered data into the consolidate range
     Rng2.Copy
     Sheet3.Range("A1").Offset(Rng1.Rows.Count, 0).PasteSpecial xlPasteValues
     Application.CutCopyMode = False
  
     Sheets("Sheet3").Activate
     ActiveSheet.Range("A1").Select
  
  End Sub
  

Here's a version of @chuff's "Pure Excel" solution that is designed specifically to work with tables. (I.E. The two data sources you want to merge are tables.)

The main difference between this method and the one chuff posted in his answer is that you don't need to define named ranges for the two data sets you are merging, since they are tables and already have their own named range. So go ahead and name your first table Table1, and your second table Table2.

Now, create a new table in the top-left corner of a new sheet and give it the same column names as the other two tables. Then enter the following formula in cell A2 of the sheet you just created:

=IFERROR(INDEX(Table1,ROWS(A$2:A2),COLUMN(A2)), IFERROR(INDEX(Table2,ROWS(A$2:A2)-ROWS(Table1),COLUMN(A2)), "-"))

Next, copy this formula across the all the table columns, and then down the rows until the results of the formulas are all dashes ("-"). Note: Sorting this new table won't do anything, since the contents of each cell are actually identical (they all contain the same formula).

If columns in the merged table display 0's when they should be displaying a blank cell, you can wrap the formula in that column with the substitute function, like this:

=SUBSTITUTE(<old expression here>, 0, "")

---

If you want to create a pivot table that uses data from this new table, you'll have to create a named range. First, name the table Table3. Now, go to the formulas tab and click "Define Name". Give the reference a name, enter the following equation for its value ("Refers to"):

=OFFSET(Table3[#All],0,0,ROWS(Table1)+ROWS(Table2)+1)

You can then use this named reference as the range for your Pivot Table.