I’ve long been a fan of Jimmy Pena’s Random Sample Data Generator Add-in and I use it quite a bit. I wanted to write my own, however, and introduce a little more flexibility. In particular, I was creating some sample inventory data and I needed to create inventory part numbers. My part numbers are of the form AIM-0000 or PP-0000. Easy enough to do with a RANDBETWEEN and some concatenation, but I wanted to build it in to the generator. So I did.
I’ve attempted to break down the samples into types. I have
- Number. A number between a minimum and a maximum with a specified number of decimal places. It can be random or sequential. If it’s sequential, it can skip by a specified amount.
- Date. A date between a minimum and a maximum. It can be random or sequential. It can skip by days, months, quarters, or years.
- Time. A time between a minimum and a maximum. It can be random or sequential. It can skip by hours, minutes, or seconds.
- Constant. This is a string that simply outputs itself over and over. This would mainly be used in compounds.
- List. A predefined list of text. It can be random or sequential. You can import lists from a text file rather than typing in all the entries.
- Text. A string of a specified length. It can be upper, lower, or proper case.
- Compound. This consists of one or more of the above types.
Here’s the main screen.
You select any number of samples and put them in the output box. An example is shown to make sure you’re getting what you think you’re getting. You can specify the number of records (defaults to 50). You can output the data to a new workbook, a new worksheet, or the active cell. To add a new sample, click the Add link above the Samples box. You are presented with a dialog to specify the properties of the sample. Here’s what the dialog for a new Number look like.
You can also edit existing samples. Here’s what editing a list looks like.
The compound type is the most interesting. The below example shows how the social security number was constructed using other sample types.
You can also add or edit existing samples from within the Add/Edit Compound dialog. You can’t delete, though, because that causes too many problems.
All of the samples can be put into categories. There is a textbox on each of the dialogs that accepts a comma separated list of category names.
I’ve already noted some bugs as I was writing this post, and I’m sure there are a few more. If you want to give it a try, let me know your questions and comments. The code is wide open, so feel free to have a look around.
You can download dkRandomData.zip
Stick the add-in somewhere and make sure the two xml files are in the same directory as the add-in. Load the add-in and look for a Quick Sample Data entry under the Tools menu (it’s under Add-in in 2007 – the Ribbon part is on my todo list).
Key to the usefulness of this, I think, is have pre-built samples in there already. If you note something that is obviously missing, let me know.