# Merging lists to create a single unique list

If you’ve ever had to combine two or more lists in Excel so that
you get a unique list from the result, you’ll know that it can be a
tedious job. A common example is two address lists with the same
the final result. They are:

files, here.

## Using a key field to get a unique list

To get a unique list using a key field, we need to go through the
following steps:

• Copy both lists to another sheet. That way, you don’t affect
the original data
• Create a formula that identifies duplicates. You may need to
create a calculated column that combines two or more columns, so
that you have a unique key; if so, you will have two calculated
columns. The first will be to create the key, and the second
will identify duplicates.
• Filter the list for duplicates, and delete these rows.

### Try it out

up, and you will see that Sheet1 and Sheet2 each have a list of
names and addresses. Both lists have the same layout, which
simplifies the process for us. There are only 18 names in the two
lists, two of which are duplicated, but the process is similar for
lists with thousands of names.

 The list on Sheet1 looks like this:

#### Create a key

Name and address details can be difficult to identify as unique.
There can be duplication in any of the name or address fields
without duplicating the whole record, so it’s best to create a key
that uses all of the available fields. In G2 of Sheet1, enter this
formula:

`=A2&B2&C2&D2&E2&F2`

Copy and paste the formula down column G to create a unique key
for each address record. Do the same on Sheet2. It isn’t pretty but
it doesn’t need to be. Once you have the combined list you can

#### Check for duplicates

In cell H2 of Sheet2, place this formula:

`=COUNTIF(Sheet1!\$G:\$G,G2)`

Fill down — records in this sheet that do not have a match in
Sheet1 will have a 0 in Column H. If there are duplicates, you will
get a value higher than 0.

#### Filter the unique records

Still on Sheet2, do the following:

• Type Key in G1
• Type Count in H1
• Autofilter the list for 0 in Column H.
• Copy the filtered records across to Sheet1, and paste them
below the existing list.
 You should now have a list of 16 addresses on Sheet1, with no duplicates. This image shows the final result.

## Using a query to extract unique data

The second option is to use the power of a database query to
extract the data into a unique list. The SQL language can combine
two or more lists into a single, unique list with a UNION query.
This approach requires the following steps:

• If the lists are in different workbooks, copy them to a new
workbook so that they are on Sheet1 and Sheet2. This has the
advantage that you won’t need to change any of the code. If you
did create a new workbook, save it.
• Paste this code into a new module in the workbook.
```''================================================
''This code combines two tables on separate sheets
''into a third worksheet in the same workbook.
''Requires: A reference to the Microsoft ActiveX
''Data Objects 2.x Library, where x is a number
''between 1 and 8.
''Assumptions: The two starting lists are on Sheet1
''and Sheet2. The combined list will go on Sheet3.
''You will need to change the sheet references in
''this code to reflect the names or your worksheets.
''Created: 29 November 2007 by Denis Wright
''================================================

Dim sSQL As String
Dim sPath As String
Dim MyConn

sPath = ActiveWorkbook.FullName

'Define the SQL statement that merges the sheets.
sSQL = "SELECT * FROM [Sheet1\$]"
sSQL = sSQL & " UNION SELECT * FROM [Sheet2\$]"

'Establish connection to the same file
'When connecting to Excel instead of a database, you need to define
'the extended properties as Excel 8.0 (The first Excel version to use ADO)

MyConn = sPath

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open MyConn
End With

'Define a recordset based on the SQL statement

rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _

Application.ScreenUpdating = False

'Delete existing data on the destination sheet, then
'transfer the results of the latest filter, starting at cell A2.
'When done, clean up references to avoid memory leaks.

With Sheets("Sheet3") 'Sheet3 is the DESTINATION sheet
.Range("A1").CurrentRegion.Clear
.Range("A2").CopyFromRecordset rst
End With
rst.Close
cnn.Close

'Copy the headings across from Sheet1 and Autofit the final list.
Sheets("Sheet1").Range("A1").EntireRow.Copy _
Destination:=Sheets("Sheet3").Range("A1")
Application.CutCopyMode = False
Sheets("Sheet3").Range("A1:F1").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub```

### Try it out

In the sample files provided with this example is a workbook
called MergingLists_Code.xls. It already has the lists in Sheet1 and
Sheet2, and a button on Sheet3 that runs the code.

The code-based method is much quicker to use than the
formula-based one. If you are starting to experiment with ADO code
in Excel, getting an understanding of SQL can save you a lot of
effort.

### How it works

SQL is a language common to all relational databases. If you
build a query in Access, and then look behind the scenes, you will
find that the graphical layout has created a SQL statement to do the
work of selecting, grouping, and sorting your data. ADO is a generic
technology that brings SQL capabilities (among other things) to
Office applications like Excel. With ADO you can interact with a
whole range of database systems, including (in this case) Excel
files.

The SQL statement in the code strings together to produce this:

`  SELECT * FROM [Sheet1\$] UNION SELECT * FROM [Sheet2\$]`

A SQL SELECT statement requires you to select one or more tables
or queries to pull your data from. When you are using a worksheet as
the data source, enclosing the sheet name in square brackets (with
the \$ symbol) causes SQL to treat the worksheet as a table.

The UNION operator will combine 2 or more tables with the
same structure
into a single list. You need to ensure that:

• The tables / sheets have the same number of fields (data
columns), and
• Those fields are in the same order in all of the lists.

If the fields are not in the same order you will get an error and
the code will fail or (possibly worse) create a garbage result.

## Creating a unique list with Advanced Filter

The third approach is also quite easy to use. The steps are:

• Copy and paste to create a starting list with all the
records, including duplicates.
• Use Advanced Filter to extract the unique items

### Try it out

In the sample files, open MergeLists_AdvFilter.xls. Sheet1
contains 16 names, and Sheet2 a further 9 names.

#### Combine the lists into one list with all records

Copy the lists from Sheet1 and Sheet2 onto Sheet3, so that you
have a single list with one set of headings.

#### Extract the unique records

below for your version of Excel.

Excel 2007

On the Ribbon, click the Data tab. The third group of icons is
Sort & Filter. In this list, select Advanced Filter.

Earlier versions

Select Data > Filter > Advanced Filter.

All versions

 The Advanced Filter dialog will display. The screen shot is for 2007, but the layout for earlier versions of Excel is virtually identical.

Make sure that you select Unique Records Only in
the bottom left corner of the dialog, then click OK. The list will
filter down to show all of the unique records. You can now select
the table, and copy the new list to another sheet.

#### Clear the filter

Sheet3 and undo the filter.

Excel 2007

In the Data tab of the ribbon, just above the Advanced button is
Clear. Push this to clear the filter and redisplay
all rows.

Earlier versions

Select Data > Filter > Show All to remove the
filter.