Forum Discussion
major_johnson
Aug 28, 2021Copper Contributor
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...
PeterBartholomew1
Aug 28, 2021Silver Contributor
SergeiBaklan's solution is perfect for your needs, as one might expect. I redid the solution from scratch and there are barely any differences
= LET(
n₁, ROWS(Column1),
n₂, ROWS(Column2),
k, SEQUENCE(n₁+n₂),
vstack, IF(k<=n₁, Column1, INDEX(Column2, k-n₁)),
SORT(UNIQUE(vstack))
)
If you are using anything but Excel 365, get rid of it and replace it by 365. If you are using 365, make sure that you allow the formula to spill and do not use CSE.
major_johnson
Aug 28, 2021Copper 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.
And yes, I do have 365 but on my personal laptop.
This is for work and they are still using 2010.
- PeterBartholomew1Aug 28, 2021Silver Contributor
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!