Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Merge Two Tables in Excel and Remove Duplicates

In this tutorial, I am going to show you 7 effective ways to merge two tables in excel and remove duplicates. You can use these methods for small or relatively larger tables. They should work fine in all cases. Also, all of these methods take very little time to understand and apply as you will see in the following sections.


Download Practice Workbook

You can download the practice workbook from here.


7 Effective Ways to Merge Two Tables in Excel and Remove Duplicates

To explain the steps clearly and in a concise manner, we have taken a simple dataset in this tutorial. It has 4 rows and 5 columns which we will vary to some extent for the later methods. Also, make sure to format your dataset as a table which will make working with them a lot easier.

merge two tables in excel and remove duplicates


1. Use Advanced Filter Option

This option in excel is the advanced version of the regular filter which helps to remove duplicates from tables. Let us see how to apply this option.

Steps:

  • First, select the second table and press Ctrl+C to copy this table.

copy the second table to merge and remove duplicates

  • Now, click on cell B8 and paste by using Ctrl+V.
  • Then, select the whole table and go to the Data tab, and then Sort & Filter.
  • Under this section, click on Advanced.

advanced filter to merge two tables and remove duplicates

  • Immediately, this will open the window, Advanced Filter.
  • Here, select the Action to Filter the list, in-place.
  • Next, insert the List range with your table range.
  • Also, check the Unique records only and click OK.

  • Finally, this will remove all the duplicate records.


2. Applying Remove Duplicates Feature to Merge Two Tables in Excel

We can remove duplicates with a single click using the Remove Duplicates feature in excel. We will see in the below steps how to use this.

Steps:

  • To start with, copy the second table as previously and paste it into cell B8.

copy one of the two tables to merge and remove duplicates in excel

  • Then, navigate to the Data tab and then Data Tools.
  • Here, click on Remove Duplicates.

selecting remove duplicates and merge two tables in excel

  • Now, in the new Remove Duplicates window, make sure to check both columns’ titles and click OK.

remove duplicates window to merge two tables in excel

  • Consequently, this will remove all the duplicates and show a message. Press OK.

  • Finally, you can check the table that there are no duplicates now.

Read More: How to Merge Two Tables Based on One Column in Excel (3 Ways)


3. Utilizing Excel Power Query

If we want to merge two tables from different workbooks and then remove any duplicates, then the quickest way to do that is by using Power Query in excel. Here is how to use it.

Steps:

  • First, navigate to the Data tab and then Get Data.

opening power query to merge two tables and remove duplicates

  • Then, click on the drop-down and hover on From File.
  • Here, select From Folder.

import files from folder to merge two tables and remove duplicates in excel

  • Now, find the folder that contains the two workbooks with the tables and select Open.

selecting files to merge two tables and remove duplicates in excel

  • Next, you will see the two file name listed and click on Combine & Transform Data.

combine & transom to merge two tables and remove duplicates in excel

  • Now, in the next window select a file from Sample File and click OK.

selecting two tables to merge and remove duplicates in excel

  • Immediately, you will see the two tables have been merged in Power Query.

  • Then, select the Name column and go to Remove Rows.
  • Here, click on Remove Duplicates.

remove duplicates in power query

  • As a result, Power Query will remove duplicate names.

  • Now, on the top-left corner click on Close & Load.

loading merge tables to worksheet

  • Consequently, this will load the merged table in your excel worksheet.

table after the merge and duplicates removal operation

Read More: How to Combine Two Tables Using Power Query in Excel


4. Using VLOOKUP Function

The VLOOKUP function in excel can look up a specific value within a table that is organized vertically. We can use this to merge two tables and then remove the duplicates. Follow the steps below.

vlookup dataset to merge two tables in excel and remove duplicates

Steps:

  • To begin with, go to cell D5 and type in the following formula:
=VLOOKUP(C5,$F$5:$G$10,2,FALSE)

vlookup formula to merge two tables and remove duplicates in excel

  • As a result, you should see that the data for the Name William is calculated.
  • Then, drag the Fill Handle to copy the formula to the cells below.
  • Next, from the Data tab, go to Data Tools and select Remove Duplicates as we saw previously.

selecting remove duplicates in excel and merge two tables

  • Then, confirm that all the columns are checked and click OK.

remove duplicates window

  • Finally, excel will remove all the duplicates and give a confirmation message.

Read More: How to Merge Two Tables in Excel Using VLOOKUP


5. Combine INDEX and MATCH Functions

The INDEX function and the MATCH function in excel allow for performing advanced lookups which can help to merge two tables that have larger datasets.

Steps:

  • First, double-click on cell D5 and enter the below formula:
=INDEX($G$5:$G$10, MATCH(C5,$F$5:$F$10,0))

INDEX and MATCH function to merge two tables and remove duplicates in excel

  • Then, press the Enter key and copy the formula to the other cells below to bring all the data.
  • Now, under the Data tab, go to Data Tools and then Remove Duplicates.

selecting remove duplicates in excel and merge two tables

  • Consequently, this will remove all the duplicates and show a confirmation window.


6. Merging Tables by Matching Multiple Columns

If we have two tables that have multiple columns in common, then we can use this method to merge them. Let us see how to do this.

merge two tables in excel with multiple common columns and remove duplicates

Steps:

  • To begin with, navigate to cell D5 and type in the below formula:
=INDEX($F$5:$H$10, MATCH(1,(B5=$F$5:$F$10)*(C5=$G$5:$G$10),0),3)

INDEX and MATCH function in excel to merge two tables and remove duplicates

  • Next, press Enter and copy the formula to the other cells below.
  • Here, you can see the duplicate records in this table.

duplicates to remove and merge two tables in excel

  • As previously, select the whole table and click on Remove Duplicates under Data.

  • As a result, you should no longer see any duplicate data.

Read More: How to Merge Two Tables in Excel with Common Column (5 Ways)


7. Merge Tables and Remove Duplicates Using VBA Code in Excel

If you want to remove duplicates from multiple worksheets, then you can use VBA to achieve this very quickly. Let us see how we can write some VBA code for this.

Steps:

  • First, select the second table and copy-paste it into cell B8.

copy the second table

  • Now, click anywhere on the table (it should be formatted as a table) and go to Table Design.
  • Under the Properties section, set the Table Name as Table1.

setting the table name after merging

  • Then, go to the Developer tab and click on Visual Basic.

opening visual basic window

  • Then, in the VBA window, click on Insert and then Module.

inserting a new module

  • Now, in the new window, type in the following code:
Sub RemoveDuplicates_tables()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ActiveSheet
Set tbl = ws.ListObjects(“Table1”)
tbl.Range.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

  • Now, select Macros under the Developer tab.

  • Then, in the new window, select the macro and click Run.

  • Finally, you can see that the duplicates no longer exist.


How to Merge Two Sheets into One in Excel and Remove Duplicates

In this method, we shall see how to bring data tables from different sheets and merge them into one sheet as a single table. Then we can use any of the previous methods to remove any duplicates.

Steps:

  • To begin with, select and copy the table in Sheet2.

copying table from other sheet

  • Then, go to Sheet1 and paste the data under the Sheet1 table using Ctrl+V.
  • Now, select the whole table in Sheet1 and click on Remove Duplicates under the tab Data.

  • Finally, this should remove the duplicates from the table.


Things to Remember

  • Make sure to form your dataset as a table.
  • If you have Excel 365, then you can use the XLOOKUP function instead of VLOOKUP.
  • Remember to insert the $ sign otherwise, the formulas will not work.

Conclusion

I hope that you understood the methods very well to merge two tables in excel and remove duplicates. Try to apply these methods to larger datasets to save a lot of time. If you get stuck in any of the steps, I recommend going through them a few times.  Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo