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 layout, and overlapping data. This article shows three ways to achieve the final result. They are:

• You can download a zip file with 3 sample 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

In the file download is a workbook called MergeLists.xls. Open it 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 delete the additional columns.

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
''================================================

Sub ADO_Merge_Sheets()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  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

  Set cnn = New ADODB.Connection
  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

  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseServer
  rst.Open Source:=sSQL, _
      ActiveConnection:=cnn, _
      CursorType:=adOpenForwardOnly, _
      LockType:=adLockOptimistic, _
      Options:=adCmdText

  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

Place your cursor in the list and then follow the instructions 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

Once you have copied the unique list somewhere else, return to 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.

Wrapping up

This article shows three different approaches for combining two or more lists into one. There is always more than one way to get the job done. These tips should increase your options if you need to tackle a chore like this.

  Home | About | Links | Contact | Excel | Access