When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data. What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales Table. What we want to know is how much each Employee made in Total Sales. Combine 2 tables. The last example shows how you can combine rows in 2 tables (in 2 different workbooks) based on criterion and insert the result in a new workbook. In this case a table with contact persons and one with company information are combined, if company names match.
This page shows examples of how to merge and combine data and tables (lists) using criteria. You can also download workbooks with the examples.
- In the first example 2 tables/lists are merged to 1 with no duplicates. The new table is put in a new workbook and sorted.
- In the second example we loop 2 lists and make 2 new: One with the shared values and one with the values that are not shared.
- The third example shows how you can combine rows in 2 tables (in 2 different workbooks) if they have a shared value or 'key' - in this case a company name. The new, combined table is put in a new workbook.
The examples use arrays, ranges, collections, the worksheet function 'CountIf' and loops.
Merge to 1 table without duplicates
This example requires some values (text or numbers) in cell A1 and down on sheet 1 and 2. Copy the code by selecting it with the mouse, press CTRL+C to copy and paste into a VBA module with CTRL+V.
You can also download a zip-compressed workbook (Excel 2003) with this and the next example.
The code merges the values from 2 lists to 1 sorted list. Even if there are shared values in the 2 lists, the output list will have no duplicates. If for instance list 1 and 2 are like below, the output list will be like the third:
Here comes the code.
Find shared and not shared values in 2 lists
Excel Join Tables Worksheet
Like the previous macro this one requires a vertical list starting in cell A1 on both sheet 1 and 2. The macro compares the two lists and makes two new: One with shared values (present in both lists) and one with non-shared values. The new lists are inserted in column J and K on sheet 1.
To check if a value from one list is present in the other, we use the spreadsheet function 'CountIf'. It counts the instances of a value in a given range. With short lists speed doesn't matter much, but with longer ones we want to minimize looping.
You can download a zip-compressed workbook (Excel 2003) with this and the previous example.
For the code above to be foolproof it should verify that the two tables do exist, and it should also check if the array has more rows than the spreadsheet. If for instance the two lists share no values, the output list will be as long as the two lists put together.
Combine 2 tables
The last example shows how you can combine rows in 2 tables (in 2 different workbooks) based on criterion and insert the result in a new workbook. In this case a table with contact persons and one with company information are combined, if company names match.
Making your own workbooks to test this example is a bit tedious, so I recommend that you download the zip-compressed example workbooks, if you want to see how it works.
The macros are in the workbook 'persons.xls' and for the stuff to work you must insert the path to 'company-list.xls' on the sheet 'Macro'.
The table with contact persons (in 'persons.xls') has the following fields/columns:
Contacts Company Tel. E-mail
The table with companies has the following fields/columns:
Company Address Postal code City Type Info
To put it short the macro loops the two tables, and if the company name matches, the data for the contact person(s) is appended. The new, combined table is put into a new workbook.
The number of columns in the combined table depends on the number of contact persons for each company.
Excel Join Tables
Important tools for the macro are a collection, dynamic arrays, ranges and the spreadsheet function 'CountIf'. The reason for using arrays (and not just ranges) is speed. Here we go with a copy of the whole VBA module:
That was it. As mentioned I recommend downloading the workbooks if you want to see the macros in action. With VBA you can automate a lot in Excel and as shown above also merging and combining data or tables.
Left Join In Excel
Join Tables In Access