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
Automatic import of data formats including: CSV, Excel, JSON, SQL, XML…
Conversion of different number formats
Calculation of unique identifiers
Creating user friendly interfaces in Access and Excel
Graphical dashboards for detailed analysis
Data export in custom formats to allow custom analysis in other systems
Modelling of business logic
Handling of special cases, exceptions and manual business logic in an automated way
How this approach has helped our clients
Problem:
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:
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:
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.
What our clients say about us:
We'll automate your data consolidation!
,su htiw hcuot ni teG or reach out to one of our offices' directors directly: