The following macro will insert a footer giving the page numbers, file loaction and name, and the date and time on the active sheet.

There are occasions when Excel might not add up properly, even though you still expect it to do so.  Lets look at a very simple example to illustrate the case. You can reproduce this yourself as you read.

I have a small table of data, four columns wide. There are two row entries.

I want a summary of  the data in the table. Specifically I am after a sum of column C where the code in column A is A, and I want the sum of column D where the code in column B is B. In both cases this should give me a result of 200 or -200.

The respective formulae to summarise by A code are =SUMIF(A:C,F2,C:C) and =SUMIF(B:D,F2,D:D). The respective formulae to summarise by B code are =SUMIF(A:C,F3,C:C) and =SUMIF(B:D,F3,D:D).

The results given....

As you can see, Excel incorrectly returns a value of -200 against the B code in the summary of column C. Did you get the same result? This is a very simplistic example to  illustrate the need to validate any report or analysis performed with Excel before publishing.

Of course, how you organise your data is also relevant. If the table were instead organised as follows

the correct result is returned.

The search function in Excel allows you to search for a text string occurrence within another text string.

Use to identify all rows where, for example, a description field, contains a certain word. Perhaps you are looking for every row which contains the word "VAT".

=SEARCH("VAT",A1,1)

where column A contains the description field.

If the string is not found the #VALUE error is returned. If the string is found you are given the starting position it occurs. In the above example, the word "VAT" starts at the third character.

To tidy up the results you can use the ISERR function to determine whether or not the error will occur. i.e. FALSE means the text is found, TRUE means it isn't.

=ISERR(SEARCH("VAT",A1,1))

Then use the IF function for a final tidy up. If the ISERR function is TRUE don't return anything, otherwise display "Matching String" in the cell.

=IF(ISERR(SEARCH("VAT",A1,1))=TRUE,"","Matching String")

Use the SEARCH function with caution. In this example I was searching for the word VAT. This letter sequence is contained in other words, such as Novation, which may not be quite what you were you looking for in your results. If you are running the function over a large dataset you might not pick up this logical error.

The following macro copies an Excel sheet to a custom template. All you need do is set the template path to your own or a network drive. This is really only a snippet to demonstrate the concept but is a complete macro nonetheless. I've used it within a more complex process to generate invoices from a data file, then copy each invoice to a separate file to email out as an attachment. This was a temporary solution during the lead up to a billing system change for a Telecoms client. 

I've used this to get around not being able to copy headers and footers in Excel and to avoid copying common drawing objects such as a logo and text boxes. By having these set in the template I didn't need to copy them each time the process looped.

If you are importing data into Excel or using an excel datasheet as a data source in, for example, an MS Query you can observe unexpected results as Excel tries to be cleverer than it is.

Only the first eight rows of your data are scanned to determine the datatype for the column, i.e. is it a number or is it text. If you have a data column which uses both, but, say only text appears in the first eight rows, the number values will not be displayed. You will be left with blanks instead of the values.

The workaround is that you have to explicitly define each of your data columns before you import your data. Do this before you import your data to avoid having to F2 through the fields (another datatype weakness in Excel !)

This is partly explained in Microsoft article KB141284 or Q141284

A DOS batch file (file with extension .bat) can be a very effective way to automate a series of file operations. You can schedule the batch file to run at a specific time, and automate, say, a series of operations such as copying, moving, deleting or renaming files. You can create a batch file in any text editor like notepad and just save the file with an extension of .bat. In windows, if you open a DOS prompt, type edit and hit return a retro text editor will open which you can also use.

The batch file code below will copy all microsoft excel files (.xls) from the current folder into a folder called "import" and append the current date in yyyymmdd format to the filename. Additionally the file extension is changed from .xls to csv (I am not sure how readable the csv file will be to other programs so let me know the result you get if you use it). 

In this example the batch file needs to be saved in the same directory as the Excel files.

There are many applications in using it. For example, sales people ftp their expense reports to a folder, these are swept up at the end of a day, the date appended to the filename, they are imported into a DB, the files are then moved to an archive folder.

When it comes to systems and applications less is more. If you find your business increasingly relying on a satellite system of spreadsheets and access databases then it really is time to review your strategy and your core systems. On a current project I've begun to feel like Copernicus gazing into the sky, except instead of stars I'm gazing out at a huge constellation of spreadsheets. In fact. there are so many, that the core system, Oracle,  is almost completely obscured from view.

Excel is great for data analysis, but it isn't the best for collecting data. Microsoft Access, on the other hand,  is a step up the ladder. You can design a better user interface and exert more control over the data entry process and consequently on the output in your reports. Although outside the scope of my remit I decided to help one group of users by replacing their shared spreadsheet with an access database - doing the work on my own time, and emphasising it was only a temporary solution.

Access, being a relational database, means your data entry screens may pull data from several tables and may only add the data to one. In building a screen that does this you might like some of your form fields to depend on the result selected in another field. This type of chained select process is very common on web forms using Javascript but it is as equally achievable in Access.

Lets have a look at a very simple database which we will call "customer". We will set up three tables.

Table 1 is called check and is used to list the types of check that will be performed on a customer.

Table 2 is related to table 1. It is a list of the possible outcomes of the check applied. Therefore it includes a foreign key (fkey) which is the index from table 1. In the example below we can see that the first three rows all relate to the credit check in table 1.

Table 3 will be the main table use to input data from a form. To keep things simple for our example this table includes the customer name as a free text field. In practice this would actually be a key to a separate customer table.

You can set these tables up in Access by using the Create table by entering data menu option. The three table are related to each other as follows.

We are now ready to create the user form, which we will name update, to input the data. Using the Create Form in Design view menu option create a form with four fields. The fourth field will contain the date time stamp which we will hide from view.

1. Customer name. Text box
2. CheckType. List box
3. CheckResult. List box
4. DateTime. Text box

To create the chained select between the two list boxes we need to do the following. Still in design view, view the properties of the CheckType list box. In the data tab we will input a SQL query in the row source as follows

Next in the event tab we need to add an event procedure to the After Update field. Click the first button at the end of the line and select [Event Procedure]. Then click the second button which will take you to a VB (visual basic) screen. Add the following code and save.

Basically here we are saying after you make a selection in the check type field, i.e. select whether the check type was a credit or a technical check, then rerun the SQL query in the check result field.

In the properties of the check result field add the following SQL query

In summary, the chained select is obtained by using the selection in the first field as a parameter in the SQL query in the second field, and by re-querying the SQL in the second field when a selection is made in the first.

I was looking for a quick way to merge the data from multiple workbooks into one worksheet in a new excel workbook.

The process of manually going through 40+ files and manually extracting the data wasn't going to be an effective use of time.

Although I used code before to do it I couldn't lay my hands on it, which hard drive, which file etc. so I had to start again but I needed a fast solution so fired up google.

One of the first links I clicked was for DigDB, an excel add-in which actually could do the job and a lot more besides but I didn't want to pay the licence. I bookmarked the site to return to later.

The solution I found was on Ron de Bruin's Excel Tips page which contains a lot of VBA code examples which you can use for free. The code there solved the problem exactly. It copies all the ranges you specify in the source workbooks and copies them into one master worksheet.

The precise code is reproduced below with some minor additional comments. To use it open an Excel workbook. Click on tools, Macro and Visual Basic Editor. In the VB editor select View and Code. Copy the code below into that page and click save. Save the file as basebook.xls on your C drive.

In the VB editor change the ranges you want to copy as required.

To use it the source files need to saved into a folder named source_data on the C drive, i.e. C:/source_data

From within basebook.xls select tools and macro. Select the macro to run.

Converting Data

This blog is also published on new IT system and is a blog on the subject of implementing new systems and preparing the underlying data using excel. There are many tools you can use to help in data migration but excel is useful to illustrate the types of issues you may face.

Migrating data between applications is not a clean mapping exercise, field sizes can be different, field types can differ, the format of data extracted will more than likely need to be changed before being loaded into a new system. Although there are many conversion tools we will look at a few examples of data which we're going to manipulate in excel. Even if you use another tool to clean data an understanding of the types of problems you may encounter will help you debug should a file you are importing into a new system fail to load correctly.

Date Formats

The date format could be any combination, i.e.

• YYYYMMDD
• DDMMYYYY
• MMDDYYYY
• etc...

Let's look at an example where the format of the date is DMMYYYY. The date is a field in our sales invoice file and we need to convert it into the format DD/MM/YYYY before we can import it into our new system.

The first thing we need to do to the string (a linear sequence of characters, in this case DMMYYYY) is to convert the field into DDMMYYYY, i.e. make all the fields consistent so that the 1st of January would appear 01012007 rather than 1012007 as it currently does. Move your mouse over cell B1 in the example below to reveal the formula.

In this example we use the text formula to convert the date field into a field with eight characters.

Once we have the field in a consistent format we can then more easily manipulate the contents. We could now split the string into days, months and years. The days is a simple left formula. The years are a right formula and the month is a combination of both. As usual moving your mouse over the cells below will show the formula

Here we use the LEFT and RIGHT formulas to extract the components of the date.

Finally we can use the DATE formula to convert to the format we need to import our transaction file

The DATE fomula works as DATE(year, month, day)

Although we have taken several steps to achieve our result we could have equally taken just one, though we are left with an ungainly looking formula

Changing Dates

We may need to change the dates in an export file as the transactions are too old to import into the current database. This may arise where we have some open invoices going back to previous financial years. There might be too much of an overhead associated with opening and closing financial years in the new system, in order to import just a few transactions; or the new system hasn't been set up to accept transactions beyond a certain date

To change the date we need to first see if it meets our condition, if it does we change the date. If it doesn't the date stays the same. Let's use an example where we have an invoice dated 31/12/2004. Our accounting year ends in December. Our new system is set up with one back year, i.e. 2005 is listed as year one. In order to post the transaction we would therefore need to modify the date (making sure to keep the old date, for reference, in a different field in the new database). Hover your mouse over cell B2 to see the formula.

The formula is a conditional argument. If the date is earlier that 01/01/2005 change the date to 01/01/2005, otherwise keep the date the same.

Fixed Field Lengths

When you export fixed length fields you can end up with whitespace forming part of the string. This may occur on, for instance, business partner codes from your legacy system. The field may have been defined with, for example, a fixed length of 7 characters but the code used was only 6. You will need to get rid of the whitespace to reduce the characters to six. To do this we will use the TRIM formula.

Creating a New Coding System

For one reason or another you may want to change your coding system. You may have outgrown your old system or you may just want something simpler. Lets assume you're going to change your supplier coding system from four alpha plus two numeric to one alpha plus three numeric. Given that we have our supplier database in excel how can we automatically generate the codes?

To do so we're going to use a combination of the formula we've used above. Obtaining the one alpha character is a simple exercise. We use the LEFT formula on the Supplier Name.

To automatically generate the numeric value of the code we need to determine how many of a given letter have already been used, so we can then allocate a new number. To do this will use a derivative of the IF formula. This is the COUNTIF formula. Hover your mouse over the cells in column C to see how we use the formula to obtain our result. Notice how in column c, using the COUNTIF formula we freeze the cell in the first part of the argument i.e. $B$1 but in the second part we only freeze the column, not the row, i.e. $B2

The formula in column C counts the number of records, in the dataset above, which start with the letter in the adjacent row cell. The formula in column D simply adds the two strings together using the ampersand &.

Field Lengths

The character length of fields in your legacy system might differ to the corresponding field length in the new system. If you try to import a file and the text in the field is greater than the field length in the new database, the file import will fail. Typically, this might arise on description fields, such as your product descriptions.

To identify which records we need to amend, before importing a file, we can use the LEN formula.

In cell C1 we've added to the formula to highlight only those records which meet a condition. Our condition checks if the length of the field is greater than twenty characters. If it is "Fix this" is displayed in the cell. If it isn't, nothing in displayed.