How to use Merge Two Tables app in Excel for Mac

Merge Tables Wizard – look up and update data in Excel for Mac

The Merge Tables Wizard app helps you copy matching rows from one table to another. You can use one or several columns as matching criteria, add new columns to the main table or update data in existing columns, highlight and filter the updated entries. Two tables can be located in the same or different workbooks.

How to:

Combine data with Merge Two Tables app for MAC

How to select the main table

The main table or Master Table is the sheet you want to update.

1. Open the Excel file or files that contain the tables you want to merge.

2. Click on the Ablebits.com tab and select Merge Two Tables.

Click on the Ablebits.com tab and select Merge Two Tables

3. Click on the Select Range button next to the Select the Master Table (first table) field and highlight the correct range.

Click on the Select Range button
Tip. You can select any cell within your Master Table before starting the Merge Tables app. The entire range will be highlighted automatically.

4. On this step, you can tick off the option Create a backup copy of the worksheet to make sure you don't lose any data.

Create a backup copy of your main worksheet

5. Click Next to proceed to step 2.

How to pick your Lookup Table

Lookup Table is the sheet where the data will be searched for (looked up). The add-in will pull information from this table.

1. On step 2, you'll get the list with all open workbooks and sheets. Select the correct lookup spreadsheet by clicking on its name.

Select the Lookup Table

2. Press the Select Range button to highlight your second table.

Press the Select Range button to highlight your second table
Tip. Before running Merge Two Tables, pick any cell within your Lookup Table. When you select this table in the list on step 2, the add-in will highlight the entire range automatically.

3. Press the Next button to continue.

How to select the matching columns

Matching or key columns are the columns with common index fields. Please note, the columns selected on this step are not updated, but matched or compared only.

1. Tick the checkboxes next to the correct Master Table columns. As you tick off the Master Table columns, the add-in automatically picks the corresponding lookup column names.

Tick the checkboxes next to the correct Master Table columns

2. If the Master and Lookup Table column names don't coincide (like with Emails and Email addresses), you can select the needed columns by clicking on the double-sided arrow and selecting the necessary column in the list.

Click on the double-sided arrow and select the necessary Lookup Table column in the list

3. On this step you can also identify if your Master and Lookup tables have headers. To do this, just select or deselect the Master Table has header and Lookup Table has header

Select or deselect the Master Table has header and Lookup Table has header checkboxes

4. Click Next to navigate to step 4.

How to update or add columns to the end of your main table

On the Choose action step of the wizard, you see 2 sections with column names: Update values and Add columns. Here you can select Lookup Table columns that will be updated or added to the end of your Master Table.

1. The Update values group shows the columns in your Master Table that can be updated with the values from the corresponding Lookup Table columns. The columns with the same names are selected automatically. Just deselect the columns you don't need to update.

Update cells in your Master Table

2. If you want to select another Lookup column name, click on the double-sided arrow next to the correct name and pick the needed column name from the list.

Click on the double-sided arrow and pick the needed column name from the list
Tip. The columns you selected on the Matching columns step are not shown on step 4.

3. The Add columns group shows Lookup Table columns you can add to the end of your Master Table. The recommended columns are selected on the wizard by default. Just deselect the columns you don't want to add by unticking their checkboxes.

Select the columns to be added to the end of your Master Table

Pick additional merging options for expected results

On the last step of Merge Two Tables you can see a number of adjusting options you can select to fine-tune the results.

1. Tick off the option Add non-matching rows to the end of your Master Table to add extra rows that are not present in the Master Table. For example, both tables contain a column with names. The main table has the names from 1 to 20. The second list contains names from 1 to 25. Thus, the names from 21 to 25 in your lookup table are non-matching. When you check theAdd non-matching rows to the end of the Master Table option, the rows with such values will be pasted after all rows of the Master Table.

Add non-matching rows to the end of your Master Table

2. Add rows with duplicate key column values from the Lookup Table. When you update the same tables together with your colleagues, there can occur duplicate values on different rows that contain unique information. Not to lose data and to add such rows to your main table, select the option Add rows with duplicate key values from the Lookup Table.

Add rows with duplicate key column values from the Lookup Table
  • Add to the end of the Master Table. If you select this checkbox, the rows that contain duplicated key values, will be added after all rows in the Master Table. Add rows with duplicate key values
  • Add after the same key value of the Master Table. If you select this radio button, the add-in will merge rows with duplicate key values in pairs. If there are extra rows with dupes in the second table, they will be inserted after the row with the same key column value. Add rows with duplicate key values after the same key value

3. Tick off the Add the Status column option to add a column that will show the changes made to the rows and mark rows as: Duplicate, Non-updated, Matching.

Select the option Add the Status column

4. If you check the box next to Update only empty and new cells in the Master Table, the add-in will fill only the empty cells. The existing values in your Master Tablewill not be overwritten with this option selected.

Update only empty and new cells in the Master Table

5. By selecting the option Update only if cells from the Lookup Table contain data you will avoid copying empty cells from your Lookup Table.

Pick the option Update only if cells from the Lookup Table contain data

6. Tick off the checkbox Set background color of updated cells to change the fill color for the updated cells. Click the arrow next to the appropriate field and pick a color from the drop-down list. After joining, all the updated Excel data in the Master Table will be highlighted with this color and you will easily see the changes.

Select the Set background color of updated cells checkbox

Now just click the Finish button and enjoy the results!

Click the Finish button and enjoy the results

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
Excel add-ins and Outlook tools - Ablebits.com