Compare Two Columns in Two Sheets to Remove Duplicates and Return One Copy Only

major_johnson 

This takes me back to stuff I was thankful to see the back of, hopefully forever!

First, defining  n₁, n₂ and 'combined'

= ROWS(Table1)
= ROWS(Table2)

= IF(k<=n₁, 
    Column1, 
    IF(k<=n₁+n₂, 
      INDEX(Column2, k-n₁),
      ""
    )
  )

one the uses MATCH to identify the first occurrence and SMALL to compact the list

= IFERROR(
    INDEX(
      combined,
      SMALL(
        IF( MATCH(combined, combined, 0) = k, k, "" ),
        @k
      )
    ),
 "" )

I have also shown a PowerQuery solution that works with Office 2010 but requires an additional software download.

let
    Source = Table.Combine({Table1, Table2}),
    Distinct = Table.Distinct(Source),
    Text = Table.TransformColumnTypes(Distinct,{{"Data", type text}})
in
    Text

All of which serves to reinforce my dislike of conventional spreadsheets!

major_johnson 

That is not CSE formula, that is regular formula which returns the spill automatically updated if you add new data into structured tables (Table1 and Table2). If I add some data into Table1

and same into Table2

resulting spill will be