Healthcare

Case Study

The Challenge

As a supplier of products for pre-clinical research, there are specific clinical terms that Tocris must not in their marketing copy, in order to comply with legislation. The challenge was to quickly identify these clinical terms within a number of different paragraphs of text so users could remove the offending terms.

Although the primary end user was relatively familiar with Microsoft Excel, they were unable figure out a way to highlight the exact location of non-compliant terms within the cell. Because of the sheer volume of copy to review, doing this manually would be extremely time-consuming and error-prone, and therefore was not an acceptable long-term solution.

The Desired Outcome

The main objective was to automate the ability to identify cells which contained non-compliant terms. The ideal solution would also highlight the words that were non-compliant within the cell, in order to allow the user to quickly identify what exactly needed to be changed, versus what could be left alone within a particular phrase.

The solution would also need to be able to handle terms that were either at the start, in the middle or at the end of a phrase, including if it was hyphenated. The user also wanted the list of words to search for to be editable, in order for them to be able to add or remove terms as time goes on.

The Solution

After performing some research into the various options available, we determined that VBA would be required to highlight specific characters within a cell. Therefore we developed a macro that would firstly highlight cells containing the offending terms, and then highlight the offending term within each paragraph of text.

Once tested on a real data set, we noticed that it could take a significant amount of time if the dataset was extremely large. After consulting with our client, we realised that for the majority of use cases, only a single column within the data set needed to be checked, so we added the option to only perform the check on a single column at a time.

The optimisation significantly speeded up the execution time of the macro and provided the end users with another option if they were under time pressure.

The Impact

Our client was delighted as not only did the solution work perfectly first time, but the entire process - from start to finish - took less than 24 hours. Tocris Bioscience has realised the following benefits:

• Greatly increased production - compared to spending hours checking copy manually by eye, the solution takes seconds to run.
• Error rate minimised - by using an algorithm which applies hard and fast rules in order to highlight the offending terms, nothing gets missed.
• Future-proof - by developing the solution to have a customisable "dictionary" of offending terms, the solution can be maintained internally by Tocris rather than relying on external developers to get involved if new phrases are needed.
• Easy-to-use - a simple user interface with clearly labelled buttons makes it almost a joy to use.

Since this project we have gone to on develop other, Excel-based tactical solutions to assist Tocris in solving their everyday business challenges.