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

In Excel, I have a spreadsheet that pulls data from a SQL Database into a table and then generates a report based on that data. Unfortunately, the data in this SQL database is incomplete and I want to include additional rows in the result set that are entered manually into the spreadsheet.

As much as I would like to, I can't just manually insert these extra rows into the table because they would get deleted whenever Excel pulls new data from the SQL Database. So instead, I'm considering creating a separate table with the same column headings on a new sheet and entering the data there, and then creating a third table on another sheet that somehow combines the rows from the table that pulls data from SQL and the table where I enter data manually. How can I accomplish this? (Or alternately, is there a better way of doing this that I'm somehow missing?)

Example:

Table 1 (From Database):

  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Foo  | 12    |
  | Mary   | 1/6/13  | Foo  | 7     |
  | Mary   | 1/6/13  | Bar  | 5     |
  | John   | 1/6/13  | Foo  | 5     |
  | John   | 1/13/13 | Foo  | 13    |

-

Table 2 (Entered Manually): 
  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Baz  | 3     |
  | Mary   | 1/6/13  | Baz  | 2     |
  | John   | 1/13/13 | Baz  | 5     |

-

Result:
  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Foo  | 12    |
  | Mary   | 1/6/13  | Foo  | 7     |
  | Mary   | 1/6/13  | Bar  | 5     |
  | John   | 1/6/13  | Foo  | 5     |
  | John   | 1/13/13 | Foo  | 13    |
  | Bob    | 1/6/13  | Baz  | 3     |
  | Mary   | 1/6/13  | Baz  | 2     |
  | John   | 1/13/13 | Baz  | 5     |

Solution 1:

Here's a pure Excel solution with no VBA. It works by using an INDEX function to step down the rows and across the columns of the SQL data until the values are exhausted and an error condition results. An IFERROR function catches the error and uses a second INDEX function to step down the rows and across the columns of the manually entered data, again until those values are exhausted and an error condition results. A second IFERROR function catches the error and returns a dash ("-"). (The SQL data must be refreshed via the Ribbon for the formulas to produce a correct result.)

Create a dynamic named range SQLDB for the SQL data in Sheet1 using the formula:

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

Create a second dynamic named range EXCELRNG for the manually entered data in Sheet2 using the formula:

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

Both of these named ranges assume that variable names are entered in row 1 of each of the two sheets.

Enter the variable names in row 1 of Sheet3 (beginning in cell A1).

Enter the following formula In cell A2 of Sheet3:

=IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),"-"))

Copy the formula across the variable name columns, and then down the rows until the results of the formulas are all dashes ("-").

It is possible as a next step to create a Pivot Table in another sheet for analysis and organization.

Again the first step would be to create a dynamic named range, say, RESULTRNG, inserting the following formula in the Name Manager input box for the named range:

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

Then create a Pivot Table in a new sheet, setting RESULTRNG as the table you want to analyze. This will filter out any trailing dashes from the formula table in Sheet3.

This works because the RESULTRNG formula counts up the total number of rows in Sheet1 and Sheet2 (excluding the header in Sheet2) and the total number of columns in Sheet1, and sets its extent based on those counts, excluding any dashes in any trailing rows (or columns) in the Sheet3 formula table.

Solution 2:

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.