Combining tables into one without Power Query

[SevenOut]

I have two tables (same worksheet), each with a single column. Both tables contain names only (Doe, John).
I would like to create a third table, single column only of all the names in Tables 1 and 2 that can be sorted alphabetically.
Preferably skipping blank cells.

So far, my attempts will only sort numerically and only data from the first table. Blank cells show as zeroes.
Have had no luck with Power Query.

 

[sandy666]

maybe Alt+D+P then filter by 0 (or blank)

 

[SevenOut]

Pivot Table Wizard, has not worked. Ends with a popup stating "This command requires at least two rows of source data..."

 

[sandy666]

So select column with data and one blank column

 

[SevenOut]

Will the Pivot Table update as new data is entered into the first two tables?

 

[sandy666]

sorry but no.

what was the problem with Power Query?

 

[sandy666]

example with PQ

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    append = Table.Combine({tbl1, tbl2}),
    filter = Table.SelectRows(append, each ([name] <> null)),
    distinct = Table.Distinct(filter),
    sort = Table.Sort(distinct,{{"name", Order.Ascending}})
in
    sort

with one query instead of 2 or even 3 queries

 

[SevenOut]

As the data periodically changes, auto updating is necessary as data on other worksheets will reference it. Is there an easy to update the power query as new data is added to the source tables?
Honestly, following these examples you've provided has been the most work with Power Query I've played with...

 

[sandy666]

two source Excel tables
one result table
if you add something to the source tables then refresh result table, that's all

 

[Amit Tandon]

I have two tables (same worksheet), each with a single column. Both tables contain names only (Doe, John).
I would like to create a third table, single column only of all the names in Tables 1 and 2 that can be sorted alphabetically.
Preferably skipping blank cells.

So far, my attempts will only sort numerically and only data from the first table. Blank cells show as zeroes.
Have had no luck with Power Query.

Enter below formula as an Array formula (Ctrl+Shift+Enter) in cell C2 and copy down:

=IFERROR(INDEX(A$2:B$20,MATCH(MIN(IF(COUNTIF(C$1:C1, A$2:B$20)+(A$2:B$20="")=0, COUNTIF(A$2:B$20,"<"&A$2:B$20)+1)),INDEX(IF(A$2:B$20="", "", COUNTIF(A$2:B$20, "<"&A$2:B$20)+1),,MIN(IF(MIN(IF(COUNTIF(C$1:C1, A$2:B$20)+(A$2:B$20="")=0, COUNTIF(A$2:B$20,"<"&A$2:B$20)+1))=IF(A$2:B$20="", "", COUNTIF(A$2:B$20, "<"&A$2:B$20)+1),COLUMN(A$2:B$20)-COLUMN(A$2)+1))),0),MIN(IF(MIN(IF(COUNTIF(C$1:C1, A$2:B$20)+(A$2:B$20="")=0, COUNTIF(A$2:B$20,"<"&A$2:B$20)+1))=IF(A$2:B$20="", "", COUNTIF(A$2:B$20, "<"&A$2:B$20)+1),COLUMN(A$2:B$20)-COLUMN(A$2)+1))),"")

This will extract Unique values from columns A & B, sorted in Ascending order. In case Uniques are not required the formula will be simpler.