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.
Practice file - join-merge-table-demo.xls (16k)
Note that the column names do NOT have to be the same.
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.
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.
We will use the following two simple tables as an example to illustrate the effect:
Practice file - join-merge-table-simple.xls (16k)
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.
Copyright © 1999-2007 All rights reserved
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