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:
To get a unique list using a key field, we need to go through the following steps:
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:|
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:
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.
In cell H2 of Sheet2, place this formula:
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.
Still on Sheet2, do the following:
|You should now have a list of 16 addresses on Sheet1, with no duplicates. This image shows the final result.|
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:
''================================================ ''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
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.
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:
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.
The third approach is also quite easy to use. The steps are:
In the sample files, open MergeLists_AdvFilter.xls. Sheet1 contains 16 names, and Sheet2 a further 9 names.
Copy the lists from Sheet1 and Sheet2 onto Sheet3, so that you have a single list with one set of headings.
Place your cursor in the list and then follow the instructions below for your version of Excel.
On the Ribbon, click the Data tab. The third group of icons is Sort & Filter. In this list, select Advanced Filter.
Select Data > Filter > Advanced Filter.
|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.
Once you have copied the unique list somewhere else, return to Sheet3 and undo the filter.
In the Data tab of the ribbon, just above the Advanced button is Clear. Push this to clear the filter and redisplay all rows.
Select Data > Filter > Show All to remove the filter.
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.