Data Cleansing Tool

(SB/GL ULPS 2005)

An Excel application which assists with complex validation of technical data from different systems.

After combining large amounts of data from two different systems, it was found that there were innacuracies in the combined data. To verify each record against the strict validation rules manually, would have taken an inordinate amount of time and be subject to human error.

Vtl proposed an automated Excel solution which would aquire raw data, process each row and flag any value which did not conform to the data validation rules. Each row consisted of a hierarchy of descriptors for a given commodity, with the list of permitted descriptors and values coming from an indexed 'Master' sheet.

Instead of users having to remember the hierarchy and validation rules, a form was used where each value updated its permitted list of values in a dropdown, depending on the hierarchy of the record. Incorrect values can then be manually corrected and the record marked as validated or rejected.

Once checked and cleansed accordingly, the data could be exported as any one of three file formats and error reports, to be fed back into the original system.

As a result of the vtl solution, a large quantity of data can now be quickly and accurately verified, greatly reducing time expenditure and human error.

To find out how we could make a project like this help your business, call +44 (0)1949 21212

(show full list)

18

Excel

(A)