Need dynamic formula to create a single list of unique values from two separate tables.

Are you using tables in the Excel structured referencing sense? If so, TableName[KeyFieldName] would autosize.

I'll assume not. Instead, the SQL queries leave their results in plain, unstructured ranges. If one key field is in A!X2:X10001 and the other in B!Y2:Y10001, and there should be nothing in the rows below the SQL query results, then best in the worksheet in which you're constructing the list of distinct key field values to calculate the bottommost rows in the current SQL query results.

The following would be in a 3rd worksheet.

CORRECTIONS

A1:

Formula:

Please Login or Register  to view this content.

B1:

Formula:

Please Login or Register  to view this content.

C1:

Formula:

Please Login or Register  to view this content.

Select A2:C2, type

Formula:

Please Login or Register  to view this content.

hold down [Ctrl] and [Alt] keys, and press [Enter]. This should produce an array formula in A2:C2.

A5:

Formula:

Please Login or Register  to view this content.

Fill A5 down into A6:A20004.

B5:

Formula:

Please Login or Register  to view this content.

B6:

Formula:

Please Login or Register  to view this content.

Fill B6 down into B7:B20004.

As long as the SQL queries produce 10,000 or fewer rows, column B in this 3rd worksheet would be the distinct values from the key field columns in the other 2 worksheets. The point here is that it's a lot easier to consolidate the key field columns from both worksheets into a single column, then determine the combined distinct values from the combined column than to do this piecemeal.