Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

DataRefiner Add-in

What does DataRefiner Add-in do ?

The Addin has four objectives:

1) It allows verification of your data. You can use DataRefiner to see or obtain reports on data types in a field of a data table. For example in a column of dates, you can see if all of the data are, in fact, dates or whether some text dates have been inserted.

2) It allows “cleaning” of your data. For example with text data, you can remove surplus blank spaces that can upset various parsing

formulas. And with numeric data, you can handle different causes of text numbers and convert them to numbers.

3) As part of cleaning and verification, we have included formulas that allow you to easily check your data for duplicates.

4) It allows extending your data by adding new fields that contain formulas extracting new information from your existing data. For examples, from a column of dates of birth, you can extract age as at any given date, or from a column of compound names, you can extract given or family names.

Our aim has been to make it easy for a user to validate, clean and extend their data without “touching” their existing data by inserting difficult formulas into their data tables.

We have avoided the use UDF's and ATP (Analysis ToolPak) functions. User Defined Functions are not robust solutions as they can upset security settings and / or will fail to resolve on other computers.
Analysis ToolPak functions require the Addin to be installed and checked as an Addin and are not translated if you open your workbook in a different Excel language version.

You can open the add-in like this :

1: Data>DataRefiner
2: Right click on a cell and choose DataRefiner in the Cell menu

On the first tab you have the following options :

1) Get a Data report (MsgBox or on a new sheet)
2) Filter the field
3) Compare fields

And insert a column with a:

1) Clean text formula (It is very important that your data is clean)
2) Duplicate / Unique formula (Easy to filter the duplicates now)
3) Text numbers to numbers (There are five different formulas, click on the formula info button for information)

Other tabs

If your first cell in the field is a Date then the Date tabs are enabled and if it is text or numeric it will enable the text or numeric tab.



This add-in is developed by Ron de Bruin and Norman Harker
We create the add-in for Excel 2000-2003 but it will also work in Excel 2007-2016.
DataRefiner Beta version 1.0