Data Integrity

[bgirl84]

What can i check to ensure integrity of data entered into a spreadsheet?

 

[Andrew Poulsom]

Your question is very vague. To get an answer you will need to be more specific.

 

[Ruddles]

The equally vague answer is: you check it as soon as it's available to you - as it's entered at the keyboard, before you import it from disk, before you export it from the database, etc.

Where exactly is your data coming from?


There are normally three levels of checking when you're validating data:-

• Error: data which is incorrect in its own right, such as a numeric digit in someone's name (although the way names are going these days, it won't be too long!); an invalid date (1st Octember or 39th March); a non-existent postcode, etc.
• Compatibility: the data is incorrect because it's incompatible with something else, for example you've asked someone to enter their date of birth and their age, or town and postcode, and the two don't agree.
• Warning: the data appears to be unusual in some way but it might be valid, for example a student sitting a GCSE exam might state his age as 10 - unusual but not impossible - or provide a foreign postcode because he doesn't live in the UK.

You should submit your data to all three levels of checking.

Never assume that data will be correct unless you can absolutely guarantee that the source of the data is 100% reliable. In particular never trust data which has been produced by a human because they are notoriously unreliable and sometimes even maleficent creatures, and when confronted by a prompt for a name, date, telephone number or postcode, they will one day, inevitably, type in a very rude word, just to see what the system does.

When presented with a userform they will try to save an incomplete or completely empty record, or press Escape, Ctrl-Break, or any other key combination they reckon might provide some light relief whilst they're waiting for their next coffee break. When faced with a worksheet they will rename the individual sheets, change the file name, mess around with the lookup tables, insert and delete rows and columns, merge cells, modify column headings, change colours and number formats - in fact they will attempt to do anything which will render your carefully-crafted code totally and utterly useless.

When processing data of uncertain reliability, it's often the case that 80% of your code is dedicated to ensuring that the data is valid to start with and coping with the consequences of it not being quite what you were anticipating.

I hope this helps in a general sort of way. The more information you provide us with, the more guidance we can give you in return.