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

**major_johnson** · 08-28-2021, 07:52 AM

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 like to request to have one master column in sheet3 that would find and remove duplicates but keep one of its originals and return the text into the master column in sheet3.

Also, this master column should update and sort whenever any new names are added into either of those columns in sheet1 or sheet2.

Please note, I would especially like to have an automated column in sheet3, this means, I would like to request for an non-cse (control-shift-enter) array column.

I have attached the excel file for your review.

Thank you very much in advance for helping out, really!

**alansidman** · 08-28-2021, 11:43 AM

With Power Query called Get and Transform Data and found on the Data Tab of the Ribbon.
Change the name of the column in Sheet2 to Column1
For each table

Get and Transform Data-->From Range or Table
In the PQ Editor

Home-->Close and Load To-->Connection Only

When you have done this for both, Click on one of the Queries in the right panel and select Edit.

On the Home Tab, Select append Queries-->Append as New
Select the two queries.

Close and Load to Existing Sheet. Select your sheet 3 and cell A1

Worksheet attached for your review.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**major_johnson** · 08-28-2021, 11:51 AM

Thank you very much for your response.

Two problems:

- The master column does not respond when I further amend or add names into any of sheet1 or sheet2 columns.
- I would really appreciate a formula where it is a mixture of index and match instead of query, please, but also, the formula is not be an array.

I hope you understand.

Thank you again, really, I sincerely appreciate the help!

**alansidman** · 08-28-2021, 01:50 PM

FYI, When you add to either of the columns, it is necessary to click on the Refresh All Icon on the Data tab for the Master to update. I hope someone else will come by and offer up a formula based solution for you as that is not my expertise when it comes to your type of issue. Good Luck.

**JohnTopley** · 08-28-2021, 03:17 PM

Try

In Sheet3 C2

=IFERROR(LOOKUP(2,1/(COUNTIF($C$1:C1,COLUMN1)=0),COLUMN1),IFERROR(LOOKUP(2,1/(COUNTIF($C$1:C1,Column2)=0),Column2),""))

COLUMN1 and COLUMN2 are dynamic named ranges for Sheet1 and Sheet2 lists

**major_johnson** · 08-29-2021, 07:00 AM

You, Sir, are the best, genius, and simply just amazing!!!

It took me days, literally!

But... but, and however, one last request is that the master column should auto-sort itself alphabetically.

The reason for that is I want to create a drop down list using that column.

It looks unprofessional because it's not sorted.

I really prefer the formula you inserted in column C in sheet3.

Would you mind if I were to expand and complicate my original inquiry furthermore?

**JohnTopley** · 08-29-2021, 09:59 AM

As you have Excel 365 there is additional functionality such as UNIQUE, FILTER, SORTBY which may (almost certainly will!) provide a better solution than the one I offered.

Non-365 solutions are likely to be array formulae which is what you do not want.

Please update your enquiry with the additional requirements.

And hope for a 365 solution.

**alansidman** · 08-29-2021, 11:36 AM

You can use the Unique function on each table and bring into the third sheet and then apply the unique function to the table/range you have created in the third sheet. See the attached where I have done that

**Fluff13** · 08-29-2021, 11:50 AM

Another option is

Formula:

Please Login or Register  to view this content.

**major_johnson** · 08-30-2021, 05:50 AM

I would like to thank everyone who has taken interest in the inquiry.

However, I would like to inform everyone that I am still using 2010 version of excel and I am really looking for old school formulas.

I do have 365 on my personal laptop but at my office we are using still the 2010 version.

Now, back to the drawing box, Sir JohnTopley's formula in column C of sheet 3 is perfectly fine but it just needs a little touch of auto sorting by using a formula which is possible because I have seen it somewhere else on a video or as such.

So, please, please, please, if there is any way of how to do it without using the 365 version, it would really make me a very happy person.

Furthermore, and to expand my original inquiry, it is kind of complicated to explain which is why I created an example sheet within my attached file.

There are three columns in the first sheet.

In the second sheet, I would like to create a table.

The heading of the table will be a drop down list of the first column in the first sheet.

This drop down list will have unique text, duplicates removed, and no blanks, but have the ability to detect and add text from the first column of the first sheet.

Whenever something is selected from the drop down list, a column will return text from the second column of the first sheet.

Again, this column list will have unique text, duplicates removed, and no blanks, but have the ability to detect and add text from the first column of the first sheet.

In front of this newly created column the text will be counted from the second column in the first sheet, by counting I mean, to count the text's duplicates.

In the second sheet, in column 2, 3, 4, headers should return text depending on the displayed text of column 2 in sheet 2 from the column 3 in sheet 1.

These headers will have the ability to detect and increase in columns whenever new text is added to column 3 in sheet 1.

So much fun, no?!

For more information, please see the attached file.

**JohnTopley** · 08-30-2021, 07:42 AM

In Lists

Named ranges: Column_1, COlumn_2 and Column_3

Consider making ranges in Sheet1 dynamic named ranges to replace the fixed ranges [e.g. $A$2:$A$232]

A2

=IFERROR(INDEX(Sheet1!$A$2:$A$232, MATCH(0, INDEX(COUNTIF($A$1:A1, Sheet1!$A$2:$A$232), 0, 0), 0)), "")

in B2

=IFERROR(INDEX(Sheet1!$B$2:$B$232, MATCH(0, IF(Example!$A$1=Sheet1!$A$2:$A$232, COUNTIF($B$1:$B1,Sheet1!$B$2:$B$232), ""), 0)),"")

in C2

=IFERROR(INDEX(Sheet1!$C$2:$C$232, MATCH(0, IF(Example!$A$1=Sheet1!$A$2:$A$232, COUNTIF($C$1:$C1,Sheet1!$C$2:$C$232), ""), 0)),"")

B& C are array formulas !

In Example

A1 Data Validation

List

=Column_1

in A3

=IFERROR(INDEX(Column_2,ROWS($1:1)),"")

Copy down

in B3

=COUNTIFS(Sheet1!$B:$B,Example!$A3,Sheet1!$A:$A,$A$1)

in C2 copy across

=IFERROR(INDEX(COLUMN_3,COLUMNS($A:A)),"")

in C3

=COUNTIFS(Sheet1!$C:$C,Example!C$2,Sheet1!$A:$A,$A$1)

Added VBA Sort routine on Sheet1

Please Login or Register  to view this content.

**major_johnson** · 08-30-2021, 01:54 PM

This is truly brilliant work! Just wow! Thank you very much, man, really!

I love both files' versions but I really prefer the one without VBA because, again, I am old school formula guy!

How I wish B & C columns weren't arrays!

But again, really, I deeply appreciate it!

Too grateful to you, buddy!

**alansidman** · 08-30-2021, 01:59 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Compare columns and remove duplicates

Need a macro to Compare multiple sheets and Remove Duplicates

EDITING compare-two-columns-remove-duplicates FORMULA

Compare 2 columns and remove duplicates in both columns when found

How to compare two columns and remove duplicates?

how do i compare two columns and remove duplicates?

[SOLVED] compare two columns and remove duplicates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions