Merging 2 spreadsheets on Excel 2010

Question

I was wondering if it was possible to merge data from 2 different spreadsheets together.

I'm looking at at world development indicators, so I have 1 spreadsheet which has human development index (HDI) values by country and another that has gini (inequality) values by country. However not all data is availible for all countries so the number of rows is not the same.

The first spreadsheet has Country in Column A and Human development value in Column B.
eg. Germany is row 65 with it's HDI value in column B.

The second spreadsheet also has Country in Column A and the gini value in Column B.
eg. Germany is row 43 with it's Gini value in column B.

(Both spreadsheets are listed by country in alphabetical order)

I would like to combine this data into 1 speadsheet where columns are:

Country | HDI | Gini

so I would want Germany in Row 65 with it's HDI value in Column B and it's Gini value in column C.

Is there an easy way to do this without manually copy and pasting country by country?

(I hope I explained this clearly, please ask if you're confused)

Answer 1

Here's another cool way, using "Data", "Consolidate". This will open up the "Consolidate" dialog form. See below.

To use "Consolidate", you have to "browse" to each sheet's set of data, select it into the "reference" line, then "Add" it to the "All references" list.

Once you have all references set, be sure to check the "Use labels" checkboxes for Top Row and Left Column. Click OK, and a new worksheet is created as shown in the 3rd panel below.

With a little practice, this is really fast!!!

Answer 2

Here's one way:

1. On a third worksheet, copy and paste just the country names from BOTH of the other 2 sheets in one column. You'll clearly have some duplicates. Then (in Excel 2007 and higher), select that column and choose "Data", "Remove duplicates". You can then sort alphabetically if you desire.

2. Next, add new headers on the 3rd sheet for "HDI" and "GINI" in cells B1, C1.

3. Set up some VLOOKUP formulas and copy them down in columns B, C, by just dragging the formula handle. You can include IFERROR to help clean it up. Note that you'll need the dollar signs in the vlookups to "lock" the table array part of the vlookup.

   See below; I used some dummy data: