Get your data together quickly...

Excel data consolidation

The availability and importance of data is ever growing and our clients are often realising that their data is not stored in central databases. Instead it is held in a variety of different locations such as different sites, inside the heads of employees, old proprietary software, stored as CSV, distributed across Excel files or SAP...

We have extensive experience in reading data from a variety of different sources and are able to collate, analyse and report findings in the most pragmatic way.

By talking to our clients, we understand their business needs and work together with the project team to assess and consider the best solutions for their business. We often find Excel is a successful solution: it is an accessible tool to employees and results can be turned around in the shortest of timescales.

Typical features

Typical features

Multiple sources

Automatic import of data formats including: CSV, Excel, JSON, SQL, XML…

Data formats

Conversion of different number formats

Unique keys

Calculation of unique identifiers

User friendly Interface

Creating user friendly interfaces in Access and Excel

Central storage

Storage of consolidated data in databases or Excel

Case study

Interactive visualisation

Graphical dashboards for detailed analysis

Data export

Data export in custom formats to allow custom analysis in other systems

Business logic

Modelling of business logic

Exception handling

Handling of special cases, exceptions and manual business logic in an automated way

Case studies

How this approach has helped our clients

Problem:

An automotive producer wanted to collect detailed sales and maintenance data for their vehicle types from about 1,500 sales outlets on a monthly base. These outlets were independent entities and not on a common network.

Solution:

Sending out Excel templates was the most pragmatic way accepted by all offices. These templates were populated and sent back by the sales outlets.

We built an Excel template with plausibility checks (assuring correct data entry) and locked it to prevent structural changes. These files will be sent to the outlets.

Based on an outlook rule all submitted files automatically go into a dedicated outlook folder.

A VBA program in the Excel consolidation tool loops through this folder, detaches each of the files, opens it and imports the data into a central sheet which is the base of pivot charts that visualise the summary results.

Problem:

A pharma producer wanted to ask all their back office employees about their job satisfaction and perceived flexibility in their working hours. They created an elaborate survey – populating it out should be also possible offline, preventing a web survey

Solution:

As all employees have access to the company’s network, we developed a central SQL database that will capture all responses.

The survey has been transformed into an Excel input template that all employees can fill out offline. Once online, the user can press a button in the tool which establishes a connection to the SQL database and uploads the entered answers.

The central project team uses PowerBI to access the data and analyse it.

Problem:

A producer of roof tiles wanted to combine their sales data from SAP with marketing data which was stored in an in-house developed CRM tool and production plans that were stored in 100s of decentral excel files.

Solution:

We used Excel’s PowerQuery to automate the import of the various data sources, calculate common keys and use these to merge the data into one big data sheet.

This is used to automatically fill PowerPoint with the key results, and also exported into an Excel file that is the base for custom manual analysis.

Testimonials

What our clients say about us:

Quick turn-around

In the last 6 months our IT made several unsuccessful attempts to use Survey Monkey or in-house tools to collect our data. With a simple Excel-approach data2impact solved our problem in less than 2 weeks.

Contact us

We'll automate your data consolidation!

,su htiw hcuot ni teG or reach out to one of our offices' directors directly:

Denmark

Germany

UK

USA