Function to intelligently concatenate/combine tables in Excel

Asked May 30 '12 at 17:05

Active May 30 '12 at 17:45

Viewed 1,334 times

0

Which function in Excel will intelligently combine tables using a common column as an index?

For example: http://s10.postimage.org/8dwh1agfd/superuser.png

asked May 30 '12 at 17:05

staticBatch

3
2

possible duplicate of [Excel: Merge two tables based on one column](http://superuser.com/questions/415729/excel-merge-two-tables-based-on-one-column) – Jeremy W May 30 '12 at 18:12

1 Answers1

1

1) Make a list of all the unique extensions (the "index" column)

Paste the extensions from one table below those of the other table so you have one long column with all the extensions (containing duplicates)
Remove the duplicates, e.g. using http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx

2) Add values for the "name" column

Create a new column next to the extensions, with the formula =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), where lookup_value is the extension in the new table to look up in the "names" table, table_array is the "names" table, and col_index_num is the column in the "names" table containing the names (column 2).

3) Do the same thing for the "scores" column

answered May 30 '12 at 17:45

Excel User

26
1

2

Alternatively, you could create a PivotTable based on Multiple Consolidation Ranges, where the two ranges are the "names" table and the "scores" table. – Excel User May 30 '12 at 18:32