Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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     |
share|improve this question
    
Is it ok for you if your manually added data is below your SQL data? Or do they have to be sorted by date. Excel won't overwrite rows that are right below of the existing SQL data rows. But if both data types have to be sorted, then the whole thing is more complicated. –  nixda Jan 16 '13 at 20:10
    
Well, I'd prefer that they be sorted but that isn't completely necessary. The important thing is that I am able to use pivot tables and other excel tools to analyse the entire data set (including both the manually and automatically populated tables). I also need to be able to continue to pull new data from SQL automatically. –  Ajedi32 Jan 16 '13 at 20:16
    
Would this behavior be a solution? After first query and after second query. And of course Excel will auto update your SQL when you open the workbook. –  nixda Jan 16 '13 at 20:38
    
That would work I guess. I'd rather have the data in a single table so I can filter, sort, etc, but like I said before that isn't entirely necessary. –  Ajedi32 Jan 16 '13 at 20:45
    
If the initial objective is to combine the two sources in a pivot table, have you tried multiple consolidation ranges (Shift+Alt+DP)? –  pnuts Jan 16 '13 at 20:47

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer
    
If I did it this way though wouldn't the combined table have to be a fixed size? And if made the table larger than it needed to be, wouldn't that mess me up if I tried to make a Pivot Table that handles data from the combined table? (Because some of the values in the table would '-' characters?) Or is there a way around these problems? –  Ajedi32 Jan 17 '13 at 16:16
    
(Btw, I generally prefer pure Excel solutions such as this one because they don't require the user to enable macros before they start working.) –  Ajedi32 Jan 17 '13 at 16:18
    
It would require some hand-maintenance to make sure the formulas captured all the data in the two tables. To avoid the dashes, you would need to copy the formulas down only to where they returned data, not dashes. –  chuff Jan 17 '13 at 16:48
    
Would it be possible to create another named range that includes only the rows from the the merged table that aren't blank (filled with '-' characters)? That way I could just set the pivot tables to get their data from that range and I wouldn't have to worry about the dashes. –  Ajedi32 Jan 17 '13 at 17:02
    
I've expanded my answer to explain how you could create a pivot table excluding the dashes. Trying to put a "dash-less" table would just transfer the dash problem to another sheet, with #N/A's in the dash cells unless the new table was sized by hand each time the number of rows in the source data changes. –  chuff Jan 17 '13 at 18:21

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.

connection settings

  • 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.

connection properties

  • 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
    
share|improve this answer
    
This looks like a pretty solid solution. I do have a couple of questions though. What triggers this macro? It just automatically run when you refresh the data connections, or do you have to push a separate button to run it manually? Secondly, this works with tables right? (Not just ranges that aren't formatted as tables) If that's the case, can't the table itself handle sorting? (Instead of the VBA macro doing the sorting, as you suggested?) –  Ajedi32 Jan 17 '13 at 16:09
1  
As it stands, you would run the macro by selecting Macros from the Developer tab and then selecting and running the macro. Two alternatives: Assign it to a custom button on the Ribbon and use that to run it; or embed it as macro code in a Worksheet_Activate event. This would run it whenever you selected the consolidation sheet. It will work with tables, which can do the sorting. However, I'm not sure whether tables automatically resort when data is added. –  chuff Jan 17 '13 at 17:11

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
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.

share|improve this answer

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.

share|improve this answer
    
good solution. thanks! –  Patrick Jul 8 '13 at 16:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.