This is a little workbook I created to eliminate the tedious manual work that had to be done – daily – by employees of an Insurance Company in Lagos.
Typically, data from an auto-generated text file (see "PRINT15.txt" file in this download package) containing life insurance data of employees of a client company had to be re-arranged from a complex horizontal format to a three-field (Age, Term, Rate) vertical/column format (so as to prepare for use in a software application used by the insurance company).
To do this, an employee of the insurance company would import the text file into Excel; then copy and paste individual rows containing the needed information from the resultant worksheet into the appropriate column in a blank worksheet containing three labeled fields – using the transpose command.
Not only was this process time-consuming, it was also prone to much error especially, if there was much data to be treated. I proposed automating the entire process using
Excel VB to them. They agreed. The result is this "Auto Data Transpose" workbook. Click the preceding link to download it now.
How To Use The Auto Data Transpose Workbook
What this small application does is to automated the task carried out manually by the staff of the company mentioned above. The end result is presented to you in form of 3 columns of data extracted from the imported PRINT15.txt data.
1. Unzip the ”AutoDataTranspose.zip” – 56KB – (click here) folder to your desktop.
Two files will be in it: a text file (PRINT15) containing data to be transposed and a workbook (AutoDataTranspose.xls) into which data will be transposed – and in which the code that makes this happen resides.
2. Open the AutoDataTranspose.xls workbook.
IMPORTANT NOTE : If a Security Warning that reads “Macros have been disabled" appears just above the formula bar (on the extreme left), you will need to click the OPTIONS tab next to that messsage. A dialog box bearing two radio buttons will appear. Click on the bottom placed radio button (labelled "Enable this content"), and click OK. If you do not follow this process when that warning appears, the automation in the workbook will NOT function.
The automated workbook has a set of command buttons at the top of the worksheet viz: “Import”, “Fill Blanks”, “Transpose” etc.
3. Click the “Import” button on the “Main” worksheet and use the “File-Open” dialog that appears to locate the “PRINT15” text file in the above unzipped folder. Then use your mouse to select it, and click “Open”.
The program will import the data in the PRINT.txt file into a new worksheet and name it “Import Data”.
4. Next, click “Fill Blanks” on the “Main” workshert. The progam will switch to the “Import Data” worksheet. All the blank cells next to data will be populated with zeros (this is necessary for data transpose to occur without error).
5. Now click “Transpose”(this freezes the screen, to make the procedure faster – you don’t see the copying and pasting happen) to initiate the Data Transpose procedure. Notice the message displayed in the status bar at the bottom left.
6. Wait till the process’ end is indicated by a message box prompt appearing. Click “Remove Zeros” to have blank rows quick-deleted.
The transposed data will be displayed in the “Main” worksheet.
The above process will all take place within a few minutes – compared to the hours/days (including inadvertent errors) it would take to do it by transposing manually.
I encourage you to try browsing through the data in the “Import Data” sheet, to get a feel for how much work would be entailed in transposing the data manually in Excel.
Final Words
It is quite possible that certain data handling tasks you do in your workplace may require a similar approach to what I’ve demonstrated above..
Don’t risk straining yourself, and making avoidable (and potentially costly) errors. You can prepare, and thoroughly test, an automated workbook like the one showcased in this article to do it all for you, saving you time, effort and cost.
It goes without saying that you will have to prepare your automation to suit the format of the source document for the data you wish to handle.
In other words, the automated workbook I’ve provided here is meant for use in executing a specific task routinely carried out in the Insurance company I created it for.
It is very unlikely that another insurance company would be able to use it “as is” – except of course it uses your source data arrived in the exact same kind of format.
This automated transpose workbook is meant to provide you a practical example or case study that can be used as a guide to developing similar solution for use in your company.
If you need further help doing that, get in touch using this contact form.
Look Out for More Excel-Automation Case Studies Like This
I’ll be creating more Excel-VB custom automation case studies like this in future. My key purpose is to give career persons a quick and dirty guide of how to use MS Excel to make their work lives easier – while simultaneously adding value to the companies they work for.
I did just that during my time in paid employment, and it made me an extremely successful employee in a relatively short period of 7 years.
Take it from me – what you learn from interacting with me on this subject can boost your career advancement prospect significantly.
BUILT-IN EXCEL SOLUTIONS DEVELOPMENT
Self-Development Academy’s Excel Heaven™ is the FIRST provider of Preprogrammed and Custom Spreadsheet based solutions in Nigeria. We offer Workbook Auditing/Optimisation and VBA Automation; Custom Spreadsheet Software Development; Sales; Job-Based Spreadsheet Coaching; and Consulting/Advisory Services on effective application of spreadsheets for business use.
See video demos at http://www.youtube.com/user/TKSolagbadeSDAc
Visit our Excel Heaven mini-site at : http://www.excelheaven.spontaneousdevelopment.com
NB: Our Custom Automated Excel Visual Basic applications are provided as-is
with no warranty of any kind. We also cannot assume responsibility for any programs provided here, or for any advice that is given since we have no control over what happens after our code or works leave us.