Excel – Preserving Greek letters on the PC

If you've used Text to columns recently, it will apply those settings to the Paste operation. I don't remember this happening before Excel 2007, but it may have. I always have to go back and set it back to tab delimited before I start copying and pasting again.

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.