 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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