Please try out our new DataRefiner Addin and report back with comments and criticisms to:
OR:
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.
This Add-in should serve as a very useful companion to the EasyFilter add-in which allows flexible and intuitive filtering of your data:
1) Use DataRefiner to validate, clean and extend your data.
2) Use EasyFilter to filter your data on existing and new parameters.
To obtain the EasyFilter add-in see:
http://www.rondebruin.nl/easyfilter.htm
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.
Download
You can download the Beta version here.
DataRefiner Beta version 1.0
Legal Stuff
This utility is distributed freely and with maximum exclusion of liability permitted by law.
Further distribution is permitted subject to:
1. Such distribution not being directly or indirectly for gain or reward, and 2. Distribution being together with these notes, and 3. Appropriate and prominent attribution to Ron de Bruin and Norman Harker.
Copyright on VBA coding and techniques used to create this program is specifically retained.
Use of this program is subject to the restriction that there should be no access to or copying
of the VBA Project that forms part of the workbook without consent of Ron de Bruin and Norman Harker
use of a password provided only by them for that purpose.
What we like to know :
1) If it is working correct in all language versions.
2) Suggestions for other useful formulas.
Thank you very much
Ron de Bruin and Norman Harker