Forum Discussion

major_johnson's avatar
major_johnson
Copper Contributor
Aug 28, 2021

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

Good Day, Friends! I have one column in sheet1 and the other in sheet2. Columns have duplicates, within itself as a column, as well as when both columns are compared to each other. I would li...
Formulas and Functions
major_johnson's avatar
major_johnson
Copper Contributor
This is all great stuff, but guys, I want to do it on 2010 and with a formula.

And yes, I do have 365 but on my personal laptop.

This is for work and they are still using 2010.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 28, 2021

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!

Resources