Merging two lists without duplicates

Contents

A classic situation: you have two lists that need to be merged into one. Moreover, in the initial lists there can be both unique elements and matching ones (both between the lists and inside), but at the output you need to get a list without duplicates (repetitions):

Let’s traditionally look at several ways to solve such a common problem – from primitive “on the forehead” to more complex, but elegant.

Method 1: Remove Duplicates

You can solve the problem in the simplest way – manually copy the elements of both lists into one and then apply the tool to the resulting set. Remove Duplicates from the tab Data (Data — Remove Duplicates):

Of course, this method will not work if the data in the source lists often changes – you will have to repeat the entire procedure after each change again.

Method 1a. pivot table

This method is, in fact, a logical continuation of the previous one. If the lists are not very large and the maximum number of elements in them is known in advance (for example, no more than 10), then you can combine two tables into one by direct links, add a column with ones on the right and build a summary table based on the resulting table:

As you know, the pivot table ignores repetitions, so at the output we will get a combined list without duplicates. The auxiliary column with 1 is needed only because Excel can build summary tables containing at least two columns.

When the original lists are changed, the new data will go to the combined table via direct links, but the pivot table will have to be updated manually (right-click – Update & Save). If you do not need recalculation on the fly, then it is better to use other options.

Method 2: Array Formula

You can solve the problem with formulas. In this case, the recalculation and updating of the results will occur automatically and instantly, immediately after changes in the original lists. For convenience and brevity, let’s give our lists names. List 1 и List 2using Name Manager tab formula (Formulas — Name Manager — Create):

After naming, the formula we need will look like this:

At first glance, it looks creepy, but, in fact, everything is not so scary. Let me expand this formula on several lines using the Alt+Enter key combination and indent with spaces, as we did, for example here:

The logic here is the following:

The formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) selects all unique elements from the first list. As soon as they run out, it starts to give an #N/A error:
The formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extracts the unique elements from the second list in the same way.
Nested in each other two IFERROR functions implement the output first of the unique ones from the list-1, and then from the list-2 one after another.

Note that this is an array formula, i.e. after typing, it must be entered in a cell that is not ordinary Enter, but with a keyboard shortcut Ctrl+Shift+Enter and then copy (drag) down to the child cells with a margin.

In the English version of Excel, this formula looks like:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), “”)

The downside of this approach is that array formulas noticeably slow down work with the file if the source tables have a large (several hundred or more) number of elements.

Method 3. Power Query

If your source lists have a large number of elements, for example, several hundreds or thousands, then instead of a slow array formula, it is better to use a fundamentally different approach, namely the Power Query add-in tools. This add-in is built into Excel 2016 by default. If you have Excel 2010 or 2013, you can download and install it separately (for free).

The algorithm of actions is as follows:

Open a separate tab of the installed add-on Power Query (if you have Excel 2010-2013) or just go to the tab Data (if you have Excel 2016).
Select the first list and press the button From Table/Range (From Range/Table). When asked about creating a “smart table” from our list, we agree:
The query editor window opens, where you can see the loaded data and the query name Table 1 (you can change it to your own if you want).
Double click on the table header (word List 1) and rename it to any other (for example People). What exactly to name is not important, but the invented name must be remembered, because. it will have to be used again later when importing the second table. Merging two tables in the future will only work if their column headings match.
Expand the dropdown list in the upper left corner close and download and choose Close and load in… (Close&Load to…):
In the next dialog box (it may look a little different – don’t be alarmed), select Just create a connection (Only create connection):
We repeat the whole procedure (points 2-6) for the second list. When renaming a column heading, it is important to use the same name (People) as in the previous query.
In the Excel window on the tab Data or on the tab Power Query Choose Get Data – Combine Requests – Add (Get Data — Merge Queries — Append):
In the dialog box that appears, select our requests from the drop-down lists:
As a result, we will get a new query, where two lists will be connected under each other. It remains to remove duplicates with the button Delete Rows – Remove Duplicates (Delete Rows — Delete Duplicates):
The finished query can be renamed on the right side of the options panel, giving it a sane name (this will be the name of the result table in fact) and everything can be uploaded to the sheet with the command close and download (Close&Load):