Excel: Merging two Tables

If addresses from several sources are to be used for a direct marketing campaign, then these have to be merged into a single table. This is not difficult if the addresses to be merged have a similar structure.

It is easiest to merge two tables using a special tool, such as the function intended for this purpose in DataQualityTools or DeduplicationWizard, available at www.DataQualityApps.com. However, it is also not difficult to accomplish using the standard functions in Excel, even if the structure of the two tables to be merged is different.

Let’s say we have two address tables with the following structure:

First table: 'Company1', ''Company2', 'Street', 'Postal code', 'City', 'Country'

Second table: 'Company', 'Street', 'Country', 'Postal code', 'City'

Thus, the only differences between the two tables are that in one of the tables there are two columns for the company name, and the column with the country is at a different location. Before we can merge the two address tables, we have to adjust their structures:

  1. In the second table, an additional column has to be inserted for ‘Company2’ behind the 'Company' column: To do so, click anywhere in the 'Street' column to make it the currently selected column. A click with the right mouse button brings the context menu onto your screen. Here, we select 'Insert Cells'. In the selection list of the small dialogue which then opens, select 'Entire Column'. The column headings for the 'Company' column and the newly inserted column could now be changed to 'Company1' and ' Company2'. However, since the table with the adjusted structure is only required in order to merge the two address tables, we can also do without making any additional changes.
  2. In the second table, the ‘Country’ column has to be moved behind the ‘City’ column: To do so, we start by marking the entire ‘Country’ column by clicking on the header of column 'D'. The ‘Country’ column should now be highlighted in blue. A click with the right mouse button brings the context menu onto your screen. Here, we select 'Cut'. We could also use the CTRL + X shortcut. The ‘Country’ column will now be highlighted with a dashed frame. Then we click on the header of column 'G', the column right behind the ‘City’ column. Column ‘G’ should now be highlighted in blue instead of column ‘D’. A click with the right mouse button brings the context menu onto your screen. Here, we select 'Insert'. We could also use the CTRL + V shortcut. Thus, we have moved the ‘Country’ column to the right location. Now, all we have to do is delete the empty column ‘D’. To do so, we click on the header of column 'D' so that it is highlighted in blue, get the context menu with the right mouse button and then select 'Delete cells'.

Good, so now both tables have exactly the same structure. Now, the merging of both the address tables is easy: At best, you go to the bottom of the second table to the last record, and mark it entirely by clicking on the cell with the row number in front of it. The last record should now be highlighted blue. Then you go all the way to the top of the table, press on the shift key and then click on the header of the second row. Now all of the rows of the address table should be highlighted blue except for the row with the column headings. Now all we have to do is copy these rows to the clipboard and then insert them into the first table. The blue-highlighted rows are copied to the clipboard by using the CTRL-C shortcut or the 'Copy' item from the context menu. Now open the first table, go all the way to the bottom to the last record, and click on the first empty cell in the first column. With the CTRL-V shortcut or with the 'Insert' item form the context menu, the last step is to insert the data from the clipboard underneath the data from the first address table. Save the changes. We do not need to save the structural modifications we made in the second address table.

If the two tables to be merged have very different structures, and certain data fields have to be merged or split before the tables can be merged, then it is recommended to use a tool such as the DataQualityTools, which you can find at www.DataQualityApps.com.

The merging of two tables increases the probability that one or the other record may appear more than once. You can read about how to remove duplicates from your tables in the article 'Deduplication with the DeduplicationWizard'. It is also possible to check that there is no more overlap between the two tables even before merging them. You can read about how to search for duplicates between two tables in the article 'Processing black lists with DataQualityTools'.

Author: Thomas Hainke