DigDB           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

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 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.

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

2. Wizard Step 1 - List1 is automatically selected.

   

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

   

   

   

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.

   

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

   

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.

   

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.

   

   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.

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



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


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

   

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

   

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

   

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

   

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.

Need more?

• Browse the complete list of DigDB's powerful features listed on the front page.
• If you see errors or things not working, check out our error reference page.

---

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB           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 and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.