Comparison between Data from Multiple Sheets

In the sheet below, there is a list of top 15 universities according to the Academic Ranking of World Universities (ARWU) for the years 2010 and 2009. Find out how many places each university on the list of 2010 moves up compared with its place on the list of the previous year.

Excel

To compare the data from the two spreadsheets in Excel, following two methods can be adopted:

1. Method 1. Copy the data from the two sheets into one sheet, sort it by the university name and then perform the calculation group by group.

Firstly, copy the data in the two tables manually. To create a clear viewing, place the rankings of the year 2009 in column D. Then, sort data by the university name. The result is shown below:

Based on the sorted data, you can compute the ranking increment:

This method is easy to operate, but data arranging and formula pasting should be conducted manually. Thus it isn’t fit for processing a table with a huge amount of data. Another problem is that since the computation needs to be conducted after sorting, the original order of data will be disrupted.

2. Method 2. Use a formula to search for the ranking in another sheet, and then calculate.
Firstly, in column D of one of the sheets, use a formula to find the ranking of the current university in another sheet:

Then calculate the difference of rankings in column E:

In Excel, the second method is simpler, but it is a bit difficult to compose the formula, and such a formula is not easy to understand, to view, or to edit.

esCalc

In both of the two esCalc sheets, you can set B2 and its homocells as the master cells for later use in a join operation.

Perform the join operation to join the rankings of year 2009 to the first sheet. To do so, select A2 in the 2009 sheet and press Ctrl+C to copy, then open another sheet, select D2 and perform a left join (check the Left join option in the dialog box). The result is shown below:

Then, in column E, calculate the ranking difference:

The design concept of esCalc makes using data from multiple tables quite simple. A join operation suffices to consolidate the selected data according to the master cells, without having to resort to difficult and complicated formulas. With esCalc, the desktop BI software, you can accomplish complex computations flexibly and rapidly, making grasping various types of formulas unnecessary, on the condition that you have a clear idea about the computing goal.