DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact

Join (merge) Tables (lists) - by columns match in Excel

Join is to combine two tables by matching the values in corresponding columns. In result, you will get a merged table which consists of the first table, plus the matched rows copied from the second table.

For example, suppose you have 2 tables (lists) - 'Race' and 'Horses' - they both have a column 'horse_name'. You want to join (merge) the 2 tables into 1 combined table, so that in the 'Race' table, you can also see each horse's age, color, body mark (i.e., the info located in 'Horses' table).

Join (merge) Tables (lists)

Join is a tough database operation which Excel does NOT support. Often, we hear users ask us, for example, "...I have data on 2 worksheets. I need to copy data in 1 column from one worksheet to the other if data in 2 other columns match. Does your program do this? I hope this makes sense...". Well, what they really need is a join. Do not waste time trying to get Excel's Match, Lookup, or VLookup to work. DigDB's join is what you really need.

Join is crucial for cross-table roll-up summary. For example, if you only have the above 'Race' table, you can do a roll-up summary of Average Finish of each Rider; But in the joined 'Race' and 'Horse' table, you can do a summary of Average Finish of each Horse Age group. This summary is only possible after you join the two lists into one list to have all the needed columns.

Join ( merge ) Tables Demo

  1. Go to List1 - the Race table. Click only 1 cell, then invoke "DigDB->Table->Join (Merge)...", a wizard will start.

    Join (merge) Tables (lists)
    Practice file - join-merge-table-demo.xls (16k)

  2. Wizard Step 1 - List1 is automatically selected.

    Join (merge) Tables (lists)

  3. Wizard Step 2 - Select List2.
    • Click into the range selector
    • Then you can go to the List2 sheet or area, click only 1 cell in List2
    • Click "Select Table(List)" button, to auto-select List2

    Join (merge) Tables (lists)

    Join (merge) Tables (lists)

    Join (merge) Tables (lists)

  4. Wizard Step 3 - Use the dropdown to select the pair of columns to match. Our case only needs one pair. To add more pairs, click 'Add Criteria'. This allows you to select joining by multiple column matching.

    Join (merge) Tables (lists)

    Note that the column names do NOT have to be the same.

    Join (merge) Tables (lists)

  5. Wizard Step 4 - Select what to be included in merged table

    The default is to include all rows from List1, and include only the matched rows from List2. And all columns from two tables are included. But you can select different options.

    Join (merge) Tables (lists)

  6. Joined (merged) table will be put in a new sheet - by default, it includes all rows from List1, the matched rows from List2, all columns from both.

    Join (merge) Tables (lists)

    Note that there is a new column Count of Matches. It is possible that when you join two lists, for a row in List1, there are 2 matched rows in List2. In this case, only the 1st matched row from List2 is included in the merged table, and the Count of Matches value will be 2.

    In our example, the Count of Matches is all 1. This means all the matches are one-to-one matches.

Join (merge) options for selecting rows

    Step4 of the Join wizard allows you to select how to merge the two tables. You can select:

    Join (merge) Tables (lists)

    We will use the following two simple tables as an example to illustrate the effect:

    Join (merge) Tables (lists)
    Practice file - join-merge-table-simple.xls (16k)

  1. Result for All Rows from List1, Matched Rows from List2 - this is the default, equivalent of a left-join

    Join (merge) Tables (lists)

  2. Result for All Rows from List1, All Rows from List2

    Join (merge) Tables (lists)

  3. Result for Matched Rows from List1, Matched Rows from List2

    Join (merge) Tables (lists)

  4. Result for Matched Rows from List1, All Rows from List2

    Join (merge) Tables (lists)

Join (merge) options for selecting columns

    By default, all the columns are included in merged table. But you can click the 'Select...' button to select the columns that you want to include in the result table.

    Join (merge) Tables (lists)

Need more?


Features | Testimonial | Download | Purchase | FAQ | Contact (free forum)

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.