Excel data comparison - use cases

Excel is a de-facto standard for data exchange in the offices. The spreadsheets are being sent by thousands every day in some organizations. These are usually status reports, project plans, infrastructure reports, data snapshots.

In some cases these reports are used to collect data from multiple data sources - they need to be updated by distributed teams and collated into one version that is subsequently used for decision making.

It is important to be sure that the data in the Excel spreadsheets is correct. It is difficult and time consuming to analyze each line of a report, especially if reports contain dozens of columns and hundreds of lines.

But how do you know that you collected all the data? How do you know that the report contains all the required inputs?

Use case 1 - Infrastructure report

Goal - collect information about infrastructure owned by teams in the organization. Compare the collected information with the data extracted from the infrastructure management system.

Approach: An Excel template is sent to the team leads, asking them to revert with the report containing information about number of PCs, printers and other equipment used by the team.

Problem: When there are 50 teams in the organization, it may be very difficult to ensure that all answers have been provided, that the format of the answers is correct and all resources owned by the team have been matched to the inventory data.

Solution: In such cases it might be easier to create a target state template of the report with a desired image of information in it - for example listing all required people expected to answer and the expected lists of resources they own, based on the information in the inventory system. By using SelectCompare to compare incoming spreadsheets with the target state template, user can quickly identify gaps in the data and address them appropriately. The person collecting the information can investigate discrepancies as they appear and react accordingly to the situation.

 Old computer


Use case 2 - software development - new feature

In other cases the spreadsheets contain snapshots of data that provide information to the teams whether their processes or applications work correctly.

An example: A software development team implements new functionality on an existing system.

Goal: Ensure that the data produced by the system is correct.

Approach: Run the old version of the application, collect data. Deploy the new feature and run the application. Compare the outputs with the baseline.

Problem: Creating separate database just to store the results may be difficult, expensive or impossible. One option is to create a table in the existing database and copy the results of the process into it.

Solution - Copy the required data into an Excel spreadsheet.You can even use SelectCompare to do it for you - you just create a data source and export the results of the query to the Excel spreadsheet.

Such baseline can be created ad-hoc and deleted once it is not needed anymore. The data can be shared among the testers and developers. The baseline can also be easily tweaked to reflect the expectations of the data comparison, where applicable.

Excel data comparison - a screenshot wit data comparison results in SelectCompare

SelectCompare can facilitate both use cases. Writing queries for Excel is very simple and in the long term can bring a huge amount of value to the organization.


Leave a comment

Please note, comments must be approved before they are published