In this episode of Smart Use of Spreadsheets I focus on comparing spreadsheets and an example of basic set algebra. It is time for Techie Stuff.  [On the background a nice tune starts and the smell of hot pizza and cola is entering the nostrils of the reader. That’s you.]

The Mood, the Mad, and the Muggle

Data is useless, unless it is used for other uses than only occupying space on a disk. Computer programs can be used to show the information on screen, but also to tag information. E.g. give me the phone numbers of pizzerias, which deliver at my home more than 6 meters below sea level. (The strange fact is that they do exist in the Netherlands. I say: “Cheese.” )

In this imaginary sanitised example I introduce a program, which compiles a list of all pizzerias in the neighbourhood of my home. It can export the list to a spreadsheet.

In the software development community there are really smart people. Another day I met a programmer, who refactored my pizza list program. The functionality remained the same, but the code was in better shape and maintainable. That last word sounded like music from my smartphone.

So I only had to test it (and like that). You know that thumb sign: =b. But comparing spreadsheets is quite boring and can affect my mood in a negative way (=p). Fortunately I do not have to be a wizard to get this tested fast and fully.

1. Start the current version of pizza list program.
2. Make a list and export it to spreadsheet with the name List Current Version.
3. Start refactored version of pizza list program.
4. Make a list and export it to spreadsheet with name List Refactored Version.
5. Open the spreadsheet List Current Version.
6. Rename the tab to Current Version.
7. Make a copy of the worksheet to a new map.
8. Save the map as Comparison Map spreadsheet.
9. Open the spreadsheet List Refactored Version.
10. Rename the tab to Refactored Version.
11. Make a copy of the worksheet to Comparison Map.

Now it is time to add some smart things in the map:

1. Add a third worksheet in the Comparison Map and call it Comparison Lists.
2. Copy the heading from the worksheet Current Version to the first row of the sheet Comparison Lists.
3. Use the wizard tool to make the following formula in cell A2 in  the sheet Comparison Lists by clicking on the right cells:=IF(‘Current Version’!A2=’Refactored Version’!A2;”OK”;”NOK”)
4. Copy the formula by placing the mouse on the right lower corner of the cell with the formula, press the left mouse button, move the mouse right until the most right cell of the heading of the table and release the left mouse button.
5. Determine the number of rows in the tab Current Version or the tab Refactored Version.
6. Select the cells with the formula in the second row in  the sheet Comparison Lists, place the mouse on the right lower corner of the selected cells, press the left mouse button, move the mouse the previous determined number of rows downwards  and release the left mouse button.
   The formula has been copied to all cells.

And finally it is time to determine the results of the test:

1. Select all cells in worksheet Comparison Lists.
2. Add new worksheet Results.
3. Paste by selecting Values.
4. Search for “NOK”.

For a Few Pizzas More

My nifty pizzeria list program works as follows:

• Find all pizzerias in the Netherlands.
• Tag all pizzerias within 20 kilometres of my home.
• Put the tagged pizzerias on the list.

In this extension of the imaginary situation there was a customer, who wanted to know all the pizzerias in Amsterdam. So he asked my company to modify the program. And of course to test it. So the program will work as follows:

• Find all pizzerias in the Netherlands.
• Tag all pizzerias in Amsterdam.
• Put the tagged pizzerias on the list.

After a while the customer changed his mind he only wants to have one phone number for every pizza brand in Amsterdam. So no more pages of Best Praised Amsterdam United Pizza. Just one line of this brand in the complete list. So the programmer modified the program, that only the phone number, who has the first position in an alphabetical order from Z to A. The first version with complete Amsterdam list was good. This means, that every step was already tested except the tagging.

1. Ask a programmer to make a list of every pizzeria in Amsterdam with the corresponding phone number by using a query in the database.
2. Export the list to spreadsheet .
3. Select the complete table without the heading.
4. Sort the table first level on pizzeria name from A to Z and second level on phone number from Z to A. (Use the Sort function in the Data tab)
5. Determine the minimal set of pizzeria names by removing duplicates. (Also a function in the Data tab)
6. Start the single line version of Amsterdam pizzeria list program.
7. Make a list and export it to spreadsheet .
8. Compare the spreadsheets using the trick described from the previous paragraph.

NB some functions may not be available in all spreadsheet programs.

Who ordered the Q&A?

After three episodes about using spreadsheets in a smart way there is a Q&A for people, who want to get more in depth information.
So Use the For…..m Please.

See You Next Post.