Business Library

Computer programs for automating much of the work of any small business are now quite easy to find, install, and use. But isn't it infuriating that whatever you select never quite does things precisely the way you want to do them? The answer might be to acquire some computer programming skills…

"Hang on a minute - that's not my core business" I hear you say! I am not talking about becoming a programmer, or trying to fill every single gap in any tool - which is probably neither advisable or possible. I am suggesting that some basic skills in an appropriate programming language can be extremely useful. Look for anything you do in your business which is computer-based and consists of either:

• a sequence of many operations performed once - but which must be correct first time, or
• some tedious operation which you find yourself repeating again and again.

Let's take a simple example from the first of these. Suppose you use a spreadsheet - for example Microsoft Excel^® - and have several thousand Part Codes loaded in a column of a worksheet. These have been accumulated over many years using a mixture of upper and lower case - depending on who entered each item of data originally. You need to produce a professional looking printed parts list which uses a consistent display format - first letter in capitals, the remainder lower case. This spreadsheet holds the definitive list currently and you need to change it first - how do you go about it?

One option would be to get someone to re-enter each Part Code in the format you have decided - but this is clearly a recipe for introducing errors. Instead, you can start by making use of the built-in program functions supplied with Excel to reformat the information. Before you do anything, take a back-up copy of the spreadsheet - just in case anything goes wrong!

In the case of Excel, the solution is actually very simple. Suppose the Part Code is in column A, and the data starts on the second row. Entering this simple formula in an unused column - on row 2 - is all that is required:

=UPPER(LEFT(A2,1)) & LOWER(RIGHT(A2,LEN(A2)-1))

Now if you copy and paste this formula into all the cells in the new column, all the codes will be changed in turn - and bingo! Here is a small sample:

To permanently replace the Part Code you might select the new column, copy it to the clipboard, then paste it into column A using "Paste Special" and choosing to paste "Values" rather than the default "Formula". This will fix the Part Codes in the new format. All that now remains is to delete the new column - and a seemingly difficult task has been completed in a few minutes!

Microsoft Excel is a good example of a tool with a lot of optional functionality. For example, if you are unable to achieve your objective using formulae, there is a programming language lurking in the background which has been provided as part of all recent Excel versions - called Visual Basic for Excel^®. This could, for example, be used to scan down the Part Codes column and produce a report of all those which do not begin with a letter:

Function GetParts()
  inrow = 2
  partcode = Worksheets("parts").Cells(inrow, 1).Value
  Do While partcode <> ""
    If Left(partcode, 1) Like "#" Then
      Debug.Print partcode
    End If
    inrow = inrow + 1
    partcode = Worksheets("parts").Cells(inrow, 1).Value
  Loop
End Function

The variable inrow is used to control which row the program is looking at. The partcode is extracted from the first column (A) of the "parts" worksheet. If this is not blank, the program continues with the if statement, which checks to see if the first character of the partcode is a number (like "#") and if so writes it on the debug panel (which is a standard part of Visual Basic for Excel). Once this check has been made, inrow is incremented and a new partcode selected. The Loop takes the program back to the "Do While" statement - which fails if the partcode is now blank (i.e. past the end of the data) and causes the program to continue to the End Function statement and stop.

Running this program will provide the required list - against the sample data shown above, it reports a single Part Code:

A more complex version could write this to a worksheet or to a text file - but you get the general idea? Just think how much easier and more reliable this is compared to scanning down thousands of Part Codes by eye!

There are other ways of achieving the same result - for example you could have inserted a column and used a formula to flag such cases or leave the cell blank otherwise:

=IF(ISNUMBER(VALUE(LEFT(A2,1))),"FLAG","")

In this case, the word "FLAG" will appear only if the first character in column A is a number - again it is rather easier to spot where Part Codes begin with a number by scanning this column than looking at all the Part Codes themselves.

So there are often many ways of getting help with these tedious or repetitive tasks which only require a basic understanding of the features provided with the application. If you use Excel, have a look at Functions and Visual Basic for Excel in the online help for more information about these if you wish.