Tuesday, 17 July 2007

Untangling Two Different Copies of the Same Excel Spreadsheet

The other week I was on the JISC infoNet stand at a regional conference and one of the delegates who came over to see what was new, said, “What happened to all those technical articles you used to write for NILTA News? Ages since we had one of them!”

Well, yes, I have to admit that I haven’t done an awful lot of programming over the last few years and any knowledge of database language that I have is probably a little outdated now. Database products seem to be updated regularly! However, not long after that conversation, I had a phone call from an ex-colleague who was now working for a local government organisation. She had been put in charge of some records that were stored on an Excel spreadsheet.

The problem was that two clerks had copies of this spreadsheet, held separately on their PC hard drives and at some point both of them had made update amendments to the data and she wanted some way of comparing the two files and sorting out the most up-to-date information.

I was able to help by suggesting a way forward, but unfortunately in a case like this there is no way of automatically determining which of two different records within a larger file holds the correct information. The solution I suggested would eliminate all those records that were the same in both files and allow her to concentrate a manual checking of the data on those that didn’t match.

By far the easiest way of matching records is to have both sets within the same spreadsheet, but I wanted to ensure that neither of the two copies were compromised. Therefore step one was to make a copy of spreadsheet “A”, leave a blank column to the right of all the data, then paste in all the data from spreadsheet “B”.


We now had two sets of data side by side – but of course not necessarily with data records in the same order as perhaps some rows had been deleted, some had been added, maybe in both but probably only in one of the sheets.

So we couldn’t be certain that any record in sheet “A” would be on the same row as a corresponding record in sheet “B”. We needed a way of highlighting which records existed in both sheets with all the same data in each of the fields. They were the only records that she could trust and she would then have to take the rest and make enquiries to ascertain which record was current.

To make things easier we created a new field for both sheet “A” records and sheet “B” records that held all the data from every field. This was fairly easy using the “&” sign to concatenate one field with another.


Using &" "& allows us to include a text space character between the two – not necessary for our purposes but it looks better! The formula =A4&B4&C4 would have produced the result “FredBloggs52 Sonder Avenue”.

The next step is to insert another field into sheet “A” and use that to compare each row to any row from sheet “B”. Remember that the corresponding record to row 4 in sheet “A” could be absolutely anywhere in sheet “B” so comparing row 4 in sheet "A" only to row 4 in the sheet "B" would likely cause disaster!

What we did was to compare the new field that held every bit of data in sheet “A” to the corresponding new field in sheet “B” but checking the entire column in sheet “B” by using a range. J4:J1025 would check each row from 4 to 1025 for a match. The syntax for the check is – persons of a nervous disposition look away now…

=ISNA(MATCH(D4,$J$4:$J$1025,FALSE))


This is the formula as given (if you are able to find it!) using Excel’s Help facility. It’s not the best solution for reasons I’ll explain later, but before I add to this already complicated formula, let's break it down step by step to see what it is doing.

To understand any formula, start within the brackets and work outwards. So, bit by bit…

“MATCH” is the innermost command and means what it says. In the inner brackets are the bits that tell it what to match.

“D4” means compare or match the contents of cell D4

“$J$4:$J$1025” means compare against the range J4 to J1025. The $ signs are there so that as we copy the formula down, although D4 will change to D5, D6 and so on, the range will stay constant. We don’t want to compare D1025 to a range of J1025:J2042, we want the range to stay constant.

“FALSE” …I’m not really sure what this does at this point! By default if the formula finds a match it will show the row number from the range in which it found the match. From the RANGE – not the spreadsheet! In our example with the range starting in row 4, if it returned 26 we would find the matching record on row 29 of the spreadsheet! If it doesn’t find a match it displays a “Value Not Found” error.

That’s fine but it’s not elegant. Far better to display the word “TRUE” if it finds a match and “FALSE” if it doesn’t. So that is what the ISNA(…) bit does. Except that it does it in reverse – it displays “TRUE” if a match is NOT found and “FALSE” if it does find a match. That is the Microsoft solution. You could head your column “Missing from sheet B” and there’s a job done. You need now to compare sheet B to sheet A of course, because new rows may have been added so now we add a new field to the far right and enter the formula:

=ISNA(MATCH(J4,$D$4:$D$1025,FALSE))

which does the same thing only the other way around! It matches row by row sheet “B” to a range in sheet “A”.


I said the solution wasn’t elegant. It’s still not. That’s simply because we have that double negative. We are looking for matching fields but the results of the formula shows “FALSE” if we find one!

So let’s add a simple command and another set of brackets to turn it round.

=NOT(ISNA(MATCH(D4,$J$4:$J$1025,FALSE)))

Now the “TRUE” and “FALSE” are the more logical way round and we can head the column “Match Found”.


There’s still a lot to do here because the results only tell us whether the whole data was found or not. The matching record could exist but may have some amended data – a different address field for instance. It is obvious to a human that Fred Bloggs living at the same address as Frederick Bloggs in the other sheet should be a match. It will not be a match to a computer!

We have an entry for Marie Stokes in each sheet. They may or may not be the same person as the entries have different addresses. Even if it is the same person there is no way of telling which address is correct! So from each side we know that all the records that show “TRUE” are common to both spreadsheets but now need to manually examine all other rows to see if the same name exists with a different address or whether the name appears only in one sheet or the other.

So now we can sort the records based on column D and then copy all the records labelled "True", from Spreadsheet "A" only, into a new worksheet. We can then delete those records from Spreadsheet "A" leaving only those records which are unique to Spreadsheet "A".

Next we can sort records based on column J and delete all the records labelled "True" from Spreadsheet "B" as, being previously matched in Spreadsheet "A", they have already been copied to the new worksheet.

This leaves only those records in either sheet that need to be manually examined for any similarities such as the Fred/Frederick Bloggs. Where there are two different addresses for what seems to be the same person, you will have to do some research as it is impossible to say which is the most up-to-date address, or even whether it is the same person.

These records can be added to the new worksheet as and when they are verified. The new worksheet now becomes the master copy and should be labelled and treated as such. Spreadsheets "A" and "B" should now be made read-only and archived or destroyed in accordance with your Records Retention Policy. You do have one...don't you?

Moral of the tale - don't ever have more than one updateable copy of anything, whether it is a database, spreadsheet or even a set of minutes. Use Records Management techniques to version control documents and if multiple copies are needed in electronic format designate a master copy and make all others read-only.

JISC infoNet has a number of online resources available on the subject of Records Management at http://www.jiscinfonet.ac.uk/records-management

Tuesday, 10 July 2007

The Three Project Variables

I see from this article on the BBC News web site that MPs are worried about the London Olympics Project.

It has a fixed deadline of course that cannot be moved so there are only two of the three project variables left to play with.

Ah - but wait! They are also worried because the budget has soared; "the Games' cost had at first been underestimated and private sector funding 'seriously overestimated'." Olympics Minister (It has a Minister!!!) Tessa Jowell has pledged to keep an "iron grip" on the budget.

So that's two out of the only three project variables either fixed or compromised. That leaves just one to play with, Tessa...

Oh no...! The Public Accounts Committee have voiced concerns that the organisers may forced to "accepting lower standards, to get the job finished."

Well that is the only variable left...

Adjusting any one of the variables affects the other two. If any variable is fixed then adjusting one of the others affects the remaining third variable dramatically. If your original costings or estimates or perception of what is achievable has not been realistic in the first place, then you are left with a nightmare.

In this case, it appears there was a rather optimistic view of how much contribution would come from the Business Sector. Also the Public Accounts Committee has warned that Risk Control is not being applied all that well. Risks need to be managed with an "iron hand" they say.

Managing Risk well does not come at zero cost, as you have to spend money on mitigating some risks, on insuring against others, on work to identify and monitor them. It does, or should, however, cost less than if no risks are identified at all.

Ignoring risks does not make them go away. So when they do turn into issues and there have been no mitigating activities to reduce their impact and no "Plan B" to swing into action, project managers are forced to manage them in crisis mode. The same managers who accused you of "worrying about things that haven't happened yet" are now the same ones saying angrily "why didn't you think of that???"

JISC infoNet has a number of free resources aimed at the Higher and Further Education sectors mainly, but equally applicable to most others. The resources include Project Management and Risk Management

Wednesday, 4 July 2007

How to Avoid Death by PowerPoint

I keep saying - there's no such thing. There are either good or bad presentations but Microsoft are not to blame!

So how can you prepare and present the killer presentation? What should you do and what should you not do?

I've written about this before in the days of overhead projectors (OHPs) in the pages of the National CMIS Board's newsletter but there is obviously still such a need. Someone prompted me to update the article in an attempt to save conference audiences everywhere from depression and intense boredom.

So let's cover everything - the presentation itself - what should it look like, how fast should it move, should it have sound, video, special effects? Speaking - what should you say, how should you say it, using microphones without fear. The way you act - where should you stand, where should you look, can you read a script, should you stay still or move about?

Oh... that's the first lesson - tell them what you are going to tell them first... then tell them!


The PowerPoint Presentation

This should be simple and not fussy. Don't try to put too much text on screen. A presentation is not an essay.

Use a simple easy-to-read font such as Arial or Verdana. Not a font that tries to look clever but can't be read easily. Not a script (joined-up-writing) or anything with flambouyant curls or even stuff that looks informal like Comic Sans. Arial is wonderful.

Keep jargon to an absolute minimum!

Use a large font. 28 is good, 24 should be a minimum. The acid test is this. Put the presentation on your desktop screen. Now hold your hand up in front of you at arm's length as though you were signalling someone to stop. Then move away from the screen until the width of your hand just covers the width of the screen.

That's how big the presentation will look to people at the back. Some of them will have worse eyesight than you have. You should be able to read the screen easily and look away, then easily find the line you were reading when you look back.

This means that you should have no more than ten lines of writing at the very most. Aim for no more than eight. Space is good!

Yooz a spel chekker!!! Nothing looks worse than someone, who the audience thinks should know better, having spelling mistakes littered all over their presentation. If you are not sure, paste it into Word and spell check it.


It's a Rainbow!

Pick two simple colours that contrast sharply with each other and that won't cause anyone with colour blindness a problem. One for the background and one for the text. Now stick with them. Use a third colour for headings or emphasis if you must but don't have so many colours that the audience is tempted to psychoanalyse you to see if they can understand why...

Having a pastel shade as a background can be good, as it will reduce any glare that you can sometimes get from a totally white background. If you are going to have a shaded background don't have it from very light to very dark as the text will disappear at some point on the screen. Have it from very light to light, or from dark to very dark if you have white or very light text.

It helps if all slides have the same colour combinations. It is tiring to the eyes if your presentation looks like a 1970s Top of the Pops programme.


You'll Believe a Word can Fly

Keep special effects to a minimum if you use them at all. If you really must, then use them for lines of text not individual words or (worst case scenario) each letter! I once sat through a presentation where each letter appeared seperately to the sound of an old typewriter. It was funny for the first word but after ten slides of tightly packed writing, half the audience were discussing methods of suicide...

The safest is to have lines of text fly on from right to left. That way they appear in the direction you would read them. Having text fly from the left jars at the audience. The words come on screen in the wrong order and they cannot read it until the effect has stopped.

Use of special effects makes the size of your presentation file grow enormously. Limit your use of them to images and even then use the same one rather than have lots of different effects.


Sound Effects

Are naff. Don't. Not even for... Not ever.

Use sound only if you have a sound file that adds to the presentation. An interview; the sound recorded at the spot where you took the photo that is on screen, eg, to illustrate how noisy a factory is; to let the audience hear a birdsong or a piece of music being discussed or something like that. No whooshing noises. No typewriter sounds (unless a picture of a typewriter is on screen because you are teaching journalists). No ray guns or aircraft etc.


Video

Can be very good as long as it is relevant. Video can make jaded audiences sit up and take notice. They don't have to be part of the PowerPoint file. You can switch to them by having them open behind the PowerPoint window and then use the Alt-Tab key combination (hold down Alt like you would the Shift key whilst tapping Tab) to bring the video to the front. If you are using Windows Media Player, once you have started the video running use the Alt-Enter key combination to maximise the video. The video will be shown full screen without any controls being visible. Use Alt-Enter again after it ends to close the window. Then Alt-Tab back to the presentation.


Speaking

Give yourself something to say. It should not all be written on screen. Reading aloud what people can read for themselves is annoying for an audience, especially if the speaker does not add anything other than the words off the screen. If needs be, write up your presentation and then cut out two screens from every three. The two that have gone become the things you say whilst the other one is on screen!

Use diagrams on screen and talk over them. Use bullet points so that you can expand on them. Use images to illustrate what you are saying. If you can't think of any image then whilst PowerPoint is running you can press the [B] key to just blank the screen. It will go black. You can even use it as a joke and say "Whoops - well never mind I'll just talk for a bit..."

An audience will find that impressive that you are not phased by an apparent equipment malfunction. When you are ready to resume the slide show just press [B] again. Spooky! But it gives you the appearance that you know what you are talking about and are confident enough not to let a mishap bother you.

Use a script if needs be. But keep looking up - don't stay head down, ignoring the audience. And read your script aloud beforehand to "test" it. We use slightly different language and syntax for writing that can sometimes sound strange when spoken aloud.

Remember to speak clearly and (worst "crime" of all presenters) do not mumble or talk in a monotone. Your voice should go up and down, you should practice this if you talk normally in a bit of a monotone. More of this under "How do I Act" later.

If it is a large room you may need to project your voice. There's nothing worse than a presenter you cannot hear properly. If you have a quiet voice or a large room, make sure you have a microphone. It doesn't need to be an expensive bit of kit - plug a cheap one into your PC and turn up the speakers!

To do this you need to make sure the mute is not on. Go through the menus - Start > All Programs > Accessories > Entertainment and open the Volume Control. Then make sure to uncheck the box for mute in the microphone column.


Microphone Techniques

Amazingly, a microphone can turn the most fearless person into a nervous wreck. Why, I have never been able to fathom out.

So here is the John Burke easy guide to showing off with a microphone. This does not entail striking any "Elvis" poses...

Ok, now breathe... No honestly... the mic will not pick up your breathing, making it sound like an old-fashioned steam engine roaring out of a tunnel. Breath normally.

In fact do everything normally! You don't have to talk in any special way, put on a "posh" voice or feel the need to point the microphone at the audience yelling "Lemme hear ya!". Just...act...normally.

Microphones do just one thing. They make any noise coming from directly in front of them come from speakers. That's all. But that is what most presenters forget. So I'll repeat it. ...any noise coming from directly in front of them...

Microphones in conference rooms whether hand-held or fixed are directional. Stand to one side and it won't pick you up. If you have a fixed mic, on a lectern for instance, then stand still behind it. Still - not rigid. You don't have to look like a corpse that's been propped up.

If you have a hand-held mic then don't act as though it's going to explode at any moment. It won't. Neither will it give you an electric shock, turn into a death ray or anything like that. It needs to point at your mouth, not at the sky or the audience. It needs to be fairly close to your mouth too - not held down at waist height. The normal recommendation is to rest the head of the mic on the front of your chin. Then it will always remain at the same distance away so you don't sound like a fly zooming past your ear - quiet, loud, quiet - and it will be pointing more or less the right way no matter what you do. The advantage to a hand-held mic is that you can turn your head to look at the screen from a lectern, to look at the audience, to look at your notes and it still works.

With a fixed mic, if you turn your head to the side your voice is no longer heading for the mic, it is heading across the screen and you will go a lot quieter. Avoid.


How do I Act?

There are not a lot of points to make here - you can act however you want, depending on how extrovert you are, but there are some basics so let's mention those.

It's amazing how many people act as if they are giving the presentation for themselves instead of the audience. Turning your back on an audience to read from the screen is - despite all the people who do it - not a good thing. If you need to refresh your memory of what's on the screen and don't have a monitor in front of you then step to the side and turn your head whilst leaving your body pointing at no less than half the audience.

Try not to stand where you would block anyone's view of the screen. People craning their necks sideways is a clue. If you cannot avoid blocking someone's view then move about a bit. Block everyone's view but not for long. Make sure that everyone gets time to read the screen.

Here's a basic that only about half of presenters seem to get right. Enjoy yourself! Be happy. Smile. That's not a joke it's a tip. Smiling makes your voice sound far more interesting and alive. I'm not saying grin at your audience like an idiot in search of a village, just try to relax and sound interested yourself in what you are saying. If something excites you, allow yourself to sound excited. You'll start to get feedback that mentions your "enthusiasm". There's no better compliment! If you as a presenter are obviously bored by your subject, how do you think the audience will react?

Obviously you will need to choose the right moment to get excited. If teaching health and safety and about to show a video of a car accident, it's not the time to say "Watch this - this is great...!"


Relax

I can't say that enough. It is the absolute key. Giving presentations is a skill that you get better at the more you do them.

A lot of people worry that their audience might know more than they do. So here are a few tips.

The audience understand that you are speaking. Therefore they assume you have knowledge. If you say just one thing that they didn't know you have proved it. If they already knew most of what you said then their reaction will be to feel good about themselves. Not too disappointed in you. If someone asks a question that you cannot answer then be upfront and say so. Tell them you'll find out and come back to them. Make sure you get contact details if it's a conference rather than a class.

Another ploy is to ask them right back - "What do you think it means?" Involve others in the discussion. The answer may well come out. Otherwise you can again say, "well I think we've covered a lot of ground there but I'll check my own understanding tonight and we'll come back to it at the next class."

Use the same ploy, by the way, to the person who asks you the meaning to something totally obvious just to make themselves look big in front of their mates. It will soon deflate them and as long as you remain innocent, asking the rest of the class if anyone else has a problem or can explain the answer, it won't come off as being a deliberate put-down.

Discussion is now open! Leave comments here or get in touch with me directly. How did you learn to love presenting?