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 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.<\/p>\n 1. Unzip the \u201dAutoDataTranspose.zip\u201d - 56KB - (click here)<\/a> folder to your desktop.<\/strong> <\/p>\n Two files will be in it: a text file (PRINT15<\/strong>) containing data to be transposed and a workbook (AutoDataTranspose.xls<\/strong>) into which data will be transposed \u2013 and in which the code that makes this happen resides.<\/p>\n 2. Open the AutoDataTranspose.xls workbook.<\/strong><\/p>\n IMPORTANT NOTE : If a Security Warning that reads \u201cMacros have been disabled\" <\/strong>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.<\/span><\/p>\n The automated workbook has a set of command buttons at the top of the worksheet viz: \u201cImport<\/strong>\u201d, \u201cFill Blanks<\/strong>\u201d, \u201cTranspose<\/strong>\u201d etc. <\/strong><\/p>\n 3. Click the \u201cImport\u201d button<\/strong> on the \u201cMain\u201d worksheet\u00a0 and use the \u201cFile-Open\u201d dialog that appears to locate the \u201cPRINT15\u201d text file in the above unzipped folder. Then use your mouse to select it, and click \u201cOpen\u201d.<\/p>\n The program will import the data in the PRINT.txt file into a new worksheet and name it \u201cImport Data\u201d.<\/p>\n 4. Next, click \u201cFill Blanks\u201d<\/strong> on the \u201cMain\u201d workshert. The progam will switch to the \u201cImport Data\u201d worksheet. All the blank cells next to data will be populated with zeros (this is necessary for data transpose to occur without error).<\/p>\n 5. Now click \u201cTranspose\u201d<\/strong>(this freezes the screen, to make the procedure faster \u2013 you don\u2019t see the copying and pasting happen) to initiate the Data Transpose procedure. Notice the message displayed in the status bar at the bottom left. <\/em><\/p>\n 6. Wait till the process\u2019 end is indicated by a message box prompt<\/strong> appearing. Click \u201cRemove Zeros<\/strong>\u201d to have blank rows quick-deleted. <\/p>\n The transposed data will be displayed in the \u201cMain\u201d worksheet. <\/em><\/p>\n The above process will all take place within a few minutes \u2013 compared to the hours\/days (including inadvertent errors) it would take to do it by transposing manually.<\/p>\n I encourage you to try browsing through the data in the \u201cImport Data\u201d sheet, to get a feel for how much work would be entailed in transposing the data manually in Excel.<\/p>\n It is quite possible that certain data handling tasks you do in your workplace may require a similar approach to what I\u2019ve demonstrated above.. <\/p>\n Don\u2019t 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.<\/p>\n In other words, the automated workbook I\u2019ve provided here is meant for use in executing a specific task routinely carried out in the Insurance company I created it for. <\/p>\n It is very unlikely that another insurance company would be able to use it \u201cas is\u201d \u2013 except of course it uses your source data arrived in the exact same kind of format. <\/p>\n 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. <\/em><\/p>\n If you need further help doing that, get in touch using this contact form<\/a>. <\/p>\n I\u2019ll 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 \u2013 while simultaneously adding value to the companies they work for. <\/p>\n I did just that during my time in paid employment<\/a>, and it made me an extremely successful employee in a relatively short period of 7 years. <\/p>\n Take it from me \u2013 what you learn from interacting with me on this subject can boost your career advancement prospect significantly.<\/p>\n Self-Development Academy's Excel Heaven\u2122 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. <\/p>\n See video demos at http:\/\/www.youtube.com\/user\/TKSolagbadeSDAc<\/a><\/p>\n
\n Excel VB to them. They agreed. The result is this \"Auto Data Transpose\" workbook<\/a>. Click the preceding link to download it now.<\/em><\/p>\nHow To Use The Auto Data Transpose Workbook<\/strong><\/h3>\n
![\"The](\"http:\/\/www.spontaneousdevelopment.com\/uploads\/transposeok0.png\")
<\/a><\/p>\n![\"MS](\"http:\/\/www.spontaneousdevelopment.com\/uploads\/transposeok1.png\"){kind=spacer}
<\/a><\/p>\n![\"Click](\"http:\/\/www.spontaneousdevelopment.com\/uploads\/transposeok2.png\")
<\/a><\/p>\nFinal Words<\/strong><\/h3>\n
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. <\/strong><\/h4>\n
Look Out for More Excel-Automation Case Studies Like This<\/strong><\/h3>\n
BUILT-IN EXCEL SOLUTIONS DEVELOPMENT<\/strong><\/h3>\n