VBA userform-driven list builder

VBA userform-driven list builder.

We built a pseudo database entirely in Excel VBA to act as a reliable and robust order entry module.

An Excel database-like application

We developed an application entirely within Excel that could handle attribute based card indexing. The end user can use the application without any Excel knowledge.

Cost effective VBA development

Mindful of the client’s budget and timelines, building this app with VBA offered a cost-effective approach and short development time.

Prototyping/modelling in Excel

By modelling algorithms and developing a fully working proof of concept in Excel, we derisked the project for the client and demonstrated feasibility before committing to a cloud based application.

Workflow management across several ecommerce services »

Sales software for European affiliate partners »

Packing optimisation in VBA for card grading affiliate »

The challenge

Our client’s business model is unique, and is built around speed and efficiency. As such, off the shelf software wouldn’t cut it. Instead, we were able to design, develop and deploy an entirely bespoke solution that not only utilised systems that they already had in-place, but improved their overall order process.

Building an application of this complexity within Excel and using VBA isn’t without its challenges, but our experience and expertise enabled us to create something that was cost-effective, quick and easy to deploy, and is familiar to our client’s team.

Our approach

Service digitisation 

This project was part of a wider effort to digitise our client’s back-of-house processes, which also included packing optimisation, an ecommerce store, workflow management and customer communication. Working across these solutions simultaneously allowed us to create a unified set of deliverables that could easily integrate with one another and deliver maximum visibility and efficiency.

A bespoke database

There’s no standard database for trading cards, which presented OnlyExcel with a great opportunity to build something entirely customised. A key priority was to move the client away from paper-based order submissions, but in a way that is easily accessible and highly customisable. Which is why we chose to deliver an Excel and VBA application.

A user-friendly UI – in Excel!

Whilst the fully-functional application lives within Excel, you could be forgiven for thinking that it’s tables, rows and columns. We chose to build a user interface within the app which clearly displayed buttons, fields and results in a way that is easy to access, search, and analyse. Teams can readily input and access data without the training or onboarding that you’d expect with feature-heavy off-the-shelf order management software.

Fully customised order process

Within the application, we were able to set custom order milestones not only specific to the client’s existing order process, but also including key fields such as a card’s variant name, year and value. Activity is tracked at every step of the order, which integrates with our customer communication process for full visibility of an order’s status.

The result

  • A bespoke, cost-effective and future-proofed solution that easily integrates with existing processes and has the ability to be upgraded to a web-based solution when required.
  • A user-friendly interface that makes it easy for staff to update fields, input data and access information, all within the application.
  • A direct API into customer emails, allowing information and updates to be easily emailed to customers throughout their order.

Other case studies

Transforming a 37-year-old business with quotation automation

Transforming a 37-year-old fabrication business with quotation automation. We saved time and improved efficiency for AIM Group – a busy
Read More

Service digitisation for a Pokémon trading card intermediate

Service digitisation for a Pokemon trading card intermediate. We took our client on a journey of digital transformation by optimising
Read More

Ace Media PR reporting in Excel

Customised Media Reporting Tool

Ace Media PR reporting in Excel

Customised PR reporting tool using exports from Ace Media

CREATING AN ALGORITHMIC TOOL TO ENHANCE ACE MEDIA REPORTS

CREATING A NEAT INTERIM SOLUTION

We created an interim tool that allowed the client to carry on using its existing sophisticated media software but produce more nuanced feedback to demonstrate their full value to clients.

REDUCING ADMIN TIME

While the reports the client wanted could be produced manually, this was time consuming and our tool enabled them to automate the process.

graph

MARKING THEM OUT FROM COMPETITORS

The new bespoke tool allowed them to produce uniquely detailed reports about the quality of the reach penetration of their campaigns.

The Client Challange

The client was already using Ace Media whose software offers a range of sophisticated PR reporting tools. This facilitated most of the reporting they required but a key part of their offering was a more nuanced approach to the results of their campaigns in which they score themselves on very specific criteria. Rather than simply reporting on the reach and penetration of their campaigns, they have developed ways to analyse the quality and depth of their impact.

They were keen to carry on using Ace Media’s products but were having to undertake certain aspects of their reporting manually and this was extremely time-consuming.

They wanted to find a cost-effective way to produce the specific reports they wanted without having to significantly reconfigure their current reporting arrangements.

Our Approach

At Only Excel we pride ourselves on our pragmatism and always strive to find clients the neatest solution to their problem. Where we can you find an off the peg solution, we will and we’re firm believers in the principle that it is not worth reinventing the wheel if it can be adapted to perform better. After working with the client to establish the additional detail they required to report and what was required to extract and analyse it from the data, we established that there was no existing tool that would meet their needs. We developed a bespoke algorithm that would create reports to match their precise requirements. Data was imported from Ace Media’s excel spreadsheets and used our algorithm to examine it for data exception and convert it into the charts and visual formats that the client required to present to their clients.

The Outcome

The new tool enabled the client to create highly sophisticated and in-depth reporting that helped differentiate them from competitors. Their ability to drill down into the quality of interactions and conversations that people were having about their client’s brands, enhanced appreciation of their offering and strengthened their relationships. 

But, perhaps most importantly, it didn’t require a costly upheaval reconfiguring their existing reporting processes. And, of course, it helped save considerable amounts of administration time which had been spent every month, manually processing the data. 

SAP Reporting in Excel

Logistics for bevarage manufacturers warehouse to increase efficiencies

SAP Reporting in Excel

Delivering logistics efficiencies for leading international beverage manufacturer.

SAP data Analysis TO OPTIMISE SUPPLY CHAIN OPERATIONS USING EXCEL

globe

stock distribution optimisation

We developed an optimisation algorithm with a university based computer scientist, then replicated it using VBA in Excel to complement the client's existing SAP software.

chart

logisitical efficiency

The tool enabled the client to maximise order fulfilment between different warehouses while minimising stock movements and reducing wastage.

clock

RAPID TURNAROUND

The client had estimated it would take around 18 months to make the necessary changes in SAP. We managed to deliver a solution that could operate using SAP exports in Excel within a matter of weeks.

The Client Challenge

One of the world’s leading soft drinks manufacturers urgently needed to increase the number of delivery vehicles they could process between two of their warehouses.

To facilitate this, they had to ensure there was optimal distribution of stock between their automated and traditional warehouses.

Their goal was to maximise the numbers of orders they could process while minimising the number of stock movements required between the warehouses.

The client wanted a tool that would process data exports from their SAP system, allocate orders for dispatch from each warehouse and specify stock movements to facilitate the dispatch.

Our Approach

After working closely with the client to understand the parameters and complexities of their existing systems and establish their goals and KPIs, we deployed a computer scientist.

We developed a weighted optimisation algorithm which ranked stock and prioritised orders based on data including current stock distribution, incoming stock, daily order configurations, minimum stock movements and BBE dates to ensure older stock was moved first.

Replicating the algorithm in VBA, we were able to develop a tool that allowed the client to process their SAP data in Excel. This enabled them to quickly process their data and identify issues such as shortfalls in stock to meet forthcoming orders.

It automated recommended order movements while allowing the client to override this manually to prioritise urgent orders when necessary. It also created outputs to advise the team whenever stock movements were required.

The Outcome

Our client was delighted with the automated SAP export analysis our tool provided.

They were able to increase the maximum vehicle throughput of vehicles from their warehouses from 180 to 250.

It also allowed them to optimise staffing levels at their conventional warehouse and ensure that the automated warehouse operated at maximum efficiency.

They saw wastage reduced as more efficient identification of BBE dates and batch numbers ensured that older stock was always shipped first.

By deploying the solution using Excel development was completed in a fraction of the time that the client expected it to take using SAP.

 

SCADA Reporting in Excel

SCADA Reporting in Excel

Automation of SCADA Reporting in Excel for International Firm

Fast SCADA reporting and improved feedback using Excel

layers

Automated SCADA Analysis

We wrote custom algorithms to automatically process the data produced by the client’s SCADA system and turn them into useful feedback reports.

speech

Enhanced Language Usability

To allow the company to use the system in multiple countries we incorporated a language translation feature that allowed the end user to choose the language to be displayed on reports.

clock

Speedy Delivery

This job required a fast turn-around (two weeks), and the client appreciated our quick response

Client Needs

An international fire training company wish to provide more detailed and immediate feedback to trainees based on SCADA outputs. With locations in 40 countries they also needed to provide outputs in different languages.

The Challenge

The client’s fire training simulators deliver intense live-fire training, replicating the heat and flames of a real fire emergency and allowing firefighters to battle situations they would find in real-world emergency situations. They capture real-time sensor data at millisecond level which is output to a SCADA SQL database.

This sensor data is then used to monitor how trainees are performing and to deliver feedback. Available SCADA reporting software was costly and not specific to their needs.

The Solution

Following an initial discussion period by phone, e-mail and remote screen sharing, we began with a prototype to demonstrate what could be done and to ensure that it had got to the heart of the client’s brief.

Next we developed a system in Microsoft Excel, using custom algorithms written in VBA, to automatically process the data produced by the client’s SCADA system and deliver:

  • dynamic charts and reports;
  • graphical representations of what was happening during a training session;
  • improved speed of feedback in the field.

As a result, the client could give much more immediate and usable feedback and analysis to its trainees – making training sessions much more engaging – whilst also saving on the cost of other SCADA reporting software.

EPOS Data Analysis

EPOS Data Analysis in Microsoft Excel

Production of KPI reports from EPOS data exports for restaurateur.

EPOS data analysis in Microsoft Excel

chart

Improved Business Insight

By compiling daily EPOS exports into useful tables of information we were able to create detailed reports on the effectiveness of restaurant promotional activity.

wallet

Time and Cost Saving

The owner avoided having to pay for a costly EPOS upgrade by getting more out of their existing data.

clock

Fast Turnaround

Building on our library of existing VBA modules meant that we could quickly produce a system that was easy for the restaurateur to use.

Client Needs

A US based restaurateur wanted to understand the effectiveness of their sales promotions.

The Challenge

The existing EPOS system did not provide their desired data analysis functionality and the client was faced with a costly EPOS upgrade or replacement.

The Solution

Data exports were available from the existing EPOS system in the form of .txt and .csv files so we created a user-friendly interface for importing and managing these files.

Using Excel’s programming language, VBA, the system compiles the daily exports into organised tables with useful KPI’s saving the client time and money and giving them the desired analysis.

CRM Database Cleanse

CRM Database Cleanse

Pre-migration CRM database cleanse, formatting and validation

CRM database cleanse and UK phone number verification

arrow-right-circle

Effective Migration

Cleaning and reformating the CRM database before migration ensured the successful upload of data to the new system.

clock

Time Saving

Client received their clean data back within 2 days, saving them weeks of time trying to clean the data and check it for exceptions manually.

refresh

Better Quality Data

By removing data exceptions and matching contacts between companies we were able to identify company contact numbers for thousands of contacts with missing information.

Client Needs

The client wanted to migrate contact records between two CRM systems that required different data formats.

The Challenge

Before completing the data upload into the new system they first needed help to clean, verify and reformat the entries however with around 80,000 inconsistent contact records, it was too large a task to complete manually.

The Solution

After discussion by telephone we agreed a clear set of rules, logic and principles to apply to cleaning up the data including:

  • removing unwanted characters and symbols from phone numbers;
  • replacing +44 UK international dialling codes and preserve leading 0’s;
  • removing non-numeric entries;
  • removing duplicate numbers within each records;
  • verify phone numbers against UK geographic, non-geographic, mobile and premium dialling codes;
  • match clients by name and copy HQ phone numbers across where departmental entries were missing.

Our VBA developers used a mixture of our existing modules in our code library and custom code to complete the operation. By automating data cleaning processes and UK phone number verification in Excel (using VBA) we were able to provide a clean set of data to the client in a short turnaround of only two days allowing them to quickly migrate the system.