• 
• 
• 
• 
• 
• 
• 
• 

 1 of 8

Gallery

Back to school

Posted on 2 Jul 2002 at 17:11

Simon Jones gets some help with his school work from PC Pro readers, and reveals the changes that have happened in the Digital Dashboard technology

A few months ago, (see issue 69), I wrote about a teacher who needed to merge test results for his pupils. Nigel Hudson had two Excel workbooks, one containing results from a class in Year 7 and the other for the same class in Year 9. His problem was that in the time between the two tests some pupils had left and others had joined the class, so the lists of names in each workbook matched neither in length nor in content. Cutting and pasting the names and results by hand would be incredibly tedious and prone to errors, so how could he reliably merge the lists of names and results?

As usual, previous experience colours our creativity. Being someone who is comfortable designing and implementing database systems, I suggested to Nigel a solution based on my knowledge of databases. I immediately saw that what he wanted to achieve was what we refer to in the database world as a full outer join; that is, all records from both tables with gaps in the results columns where there's no matching data.

I set about explaining how to implement such a solution using Microsoft Query with an Excel workbook to return a full outer join of his two sets of data. My solution works and, although it's cumbersome to set up, particularly if you don't know SQL, it works reliably and reasonably quickly.

I've had lots of mail about that article: some people wanted more information on how the solution worked, while others thought they had a better way of doing it. A couple of these 'better ways' still relied, to some extent, on cutting and pasting names and results, which is all very well when there are only eight names in the list, but when you've got 300 pupils in the year - and 15 have left while 20 have joined - it creates enormous potential for error. No, what we're looking for is a fully-automated way to merge lists of names and results.

Two people have written in with workable, potential solutions that don't require the use of SQL: step forward David Bradbeer and Stephen Cox.

David's solution works by applying some lateral thinking and the VLOOKUP() function. He suggests that you copy all the names from Year 7 and Year 9 into one column, then sort them alphabetically. Let's assume this is column D. This will give you a column of names where most (but not all) will be repeated. In the next column (E) you put the formula =IF(D2<>D3,D2,"") and fill down to the end of the names, which will result in the column showing all the names once only. There will be blanks in rows where a name is repeated, but they doesn't matter at this stage. In the next column (F) you put a call to the VLOOKUP function, =(VLOOKUP(E3, A$2: B$6,2,FALSE)), which will look up a name in the range A2:B6, where the Year 7 results are kept, and will display the result for that name. Column G contains the corresponding function to find the Year 9 result, =VLOOKUP(E3,A$10: B$15,2,FALSE), the range A10:B15 being where the Year 9 results are. Where the IF statement has caused a blank cell instead of a name, these VLOOKUP functions will show #N/A - but again, this doesn't matter. Next, copy the columns containing the IF and VLOOKUP functions, but use Paste Special to paste their values rather than their formulae. Now sort the pasted values by the name column to bring all the blank names and #N/A values to the top. These cells can be deleted. Finally, use Find and Replace to replace the #N/A values with nothing, just to tidy up the display.

I like this method. It may use many steps, but each one is small and easily understood. And while the VLOOKUP() function is a little arcane, it's certainly easier to explain than a full outer join.