Excel data comparison - use cases

Excel is a de-facto standard for data exchange in offices. The spreadsheets are being sent by thousands every day in some organizations. These are usually status reports, project plans, infrastructure reports, and 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 the number of PCs, printers, and other equipment used by the team.

Problem: When there are 50 teams in the organization, it may be difficult to ensure that all answers have been provided, that the format of the answers is correct, and that 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 a list of 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, the 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.

 

...

Use case 2 - software development - new feature

In other cases, the spreadsheets contain snapshots of data that provide information to the teams on 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, and collect data. Deploy the new feature and run the application. Compare the outputs with the baseline.

Problem: Creating a 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.

A baseline like this 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.

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