Home | Download | Buy | Support | Contact

Help for PATools Data Toolkit

Tip: Use your browser's 'Find' (often Ctrl+F) to search within this page.

Using the software

It is important to understand the difference between the active cell and the start cell.

The active cell is the cell where the cursor resides on the worksheet at any point in time.

The start cell is the active cell when the Toolkit is first activated. However, once a tool has been run it is the cell at which the last tool was started (ie the active cell when 'Run' was last pressed).

Setting the range to work on (top section of screen)

There are two ways to select the cells for the majority of the tools to work on - you can use "Run down columns" to select a number of columns and a point at which the Toolkit is to stop in each column, or you can use "Run on range" and specify a range.

The tool will stop before operating on the stop cell selected.

A. Run down columns

First you must set the number of columns to run on, and then the stop point in each column.

A1. Set the number of columns

When running on more than one column, once the Toolkit has found the stop point for a column (which may be at a different point in each column), it will return to the start cell, move across one column to the right, and then run again as if that was a new start. This will continue until the tool has run either on the number of columns selected or on the last column selected.

1. Number of column(s) allows you to run the tool on more than one column to the right.

2. End at column allows you to put in the letter of the last column on which you wish the tool to operate.

Tip: Double-clicking on the words 'End at column' will automatically put in the last column used on the current sheet.

A2. Set the stop point for each column

1. Stop on allows you to specify that the tool should stop on reaching a specified number of consecutive empty cells, or when a number of cells have been reviewed (including the start cell).

2. Stop at allows you to specify that the tool should stop when it reaches a cell containing some specified value or text within either the text, the value, or the formula of the cell.

Tip: If in doubt whether to use text, value or formula, click on "Information" to examine a few cells.

Alternatively you can specify that the tool stops at a certain row.

Tip: Double-clicking on the word 'row' will automatically put in the last row used on the current sheet, and similarly 'End at column'.

Within text searches, there is the option for certain characters to represent themselves or to be used as wildcards.

Note: The default for the text string is '#' which has been chosen (with wildcards switched off) as being a convenient marker to put in quickly at the end of a column of data since the key for this symbol (on a UK keyboard) is adjacent to the return key.

Tip: Double-clicking on the word 'text' will allow you automatically to put the selected text at the bottom of the current set of data; right-clicking will insert/remove the selected text at the bottom of that column of used cells.

3. Subject to using the stop point specified above there are two checkboxes available which can be used to interrupt and prompt you whether to continue either when a certain row is reached and/or when a cell containing an error is encountered.

4. All Cells will automatically select the last row and last column used on the worksheet, and set the active cell to the first visible cell.

Tip: To return to the current start cell use the button.

B. Run on range

1. Range allows you to type in a range of cells or a named range. You can also use the adjacent '...' button to select a range. The 'Get current selection' button will pick up any shaded area you have already on the worksheet.

NB The Toolkit cannot operate over multiple-selections eg C5:E11,H10:J21,L13:N22.

2. All Cells will automatically select the last row and last column used on the worksheet, and set the active cell to the first visible cell.

Tip: To return to the current start cell use the button.

3. There is one checkbox available which can be used to interrupt and prompt you whether to continue when a cell containing an error is encountered.
 

top

The 5 tabs (middle left section of screen)

Tab 1

This set of tools will carry out an activity on either the cells or the rows when finding a cell that matches the given criteria.

For example it can clear the contents of any cell which is a duplicate of the one above - to do this select the 'clear contents' and 'cells' option buttons under activity, and the 'duplicate cells' option button under criteria.

It is strongly recommended that you experiment with this tool using dummy data in order to become familiar with its use and the available options, and thereby make best use of one of the best and most powerful set of tools within the Toolkit.

Activity

The activity selected will be carried out on the cells that match the criteria (or the rows of those cells if 'Rows' is selected).

First you should always decide whether the activity is to be performed on the individual cells or on the whole rows of matching cells.

Secondly you select the activity to be performed.

1. Stop & show me gives you the option to modify cells on an individual basis as well as merely seeing the matches, and it gives a running total of the value of the matching cells.

You also have the option to make individual changes to the contents of cells, and will be prompted if you change them.

In addition you can modify the font, fill, alignment, format and/or border of individual cells. If you select one of these buttons and leave the resulting dialogue box with the 'OK' button it will be treated as a change to the cell for the purposes of the statistics, irrespective of whether you actually make a change or not. In order for it not to be counted, you must leave using the 'Cancel' button.

2. Count & tell me runs immediately because it makes no changes to your data.

3. Make font and Make fill both allow you to modify the colour of the matching cells, and the font can have bold, italics or underline turned on or off.

The window where you make your selection allows you to select either a common matching colour (with its corresponding index number), or a different colour index number (from 1 to 56 inclusive) in the box and press the 'Other' button.

Note: Although you can set the font or fill colour index in the range 1 to 56, the cells may have numbers such as -4105 or -4142 showing using 'Details': these are the values representing 'Automatic' font or 'No Fill'. To reset to these values use the large 'Reset' button.

Tip: You can hover over or click on "Information" to see the font and fill colour index number for any particular cell.

4. Format will apply the formatting you specify to matching cells.

5. Clear will reset matching cells as you specify eg formats, comments, contents, hyperlinks, conditional formatting, or all.

6. Select leaves all matching cells selected, perhaps to permit copying and pasting elsewhere. In this case the first selected cell is the active cell upon completion; to return to the start cell use the button, and this will cancel the selections. However, if you do copy and paste the selection elsewhere this will cancel the original start cell.

Note: Excel does limit the number of cells that can be selected, and this may vary from PC to PC depending on the PC set-up, capacity etc.

7. Replace contents allows you to specify some new contents for matching cells (not applicable to rows). For example you may wish to replace error formulae with the value 0.

8. Copy or move moves (cut and paste) or copies to either a new worksheet which the Toolkit will create for you in front of the sheet you are working on, or a column (1-99 columns away, left or right, existing or new) on the current worksheet. 'Move & delete' will cut and paste your data, and then delete the matching cell / the row of the matching cell, whereas 'Move' will merely cut and paste the cell and leave the old cell.

Note: If the copy button is already selected then you will need to double-click to see the window of options, or click on a different option and then click on 'Copy' again.

9. Copy and paste copies the start cell (or row of the start cell) and pastes it over the top of all matches found.

10. Insert will move cells down and Delete will move cells up.

11. Hide and Unhide operate only on rows.

12. Reset first resets all formatting (ie resets the number format, the alignment, the font, the border, the pattern and the protection to the default values) and unhides rows on matching cells BEFORE the activity is carried out (but AFTER the criteria has been matched).


Criteria

The activity selected will be carried out on the cells (or the rows of those cells) that match the criteria.

1. All cells matches every cell.

It is possible to set the criteria relating to the type of cell contents, the actual cell contents, or the cell formatting. You do this by ticking the relevant check box(es), and specifying the match required below it.

If you select more than one there is the option to Match any or Match all - ie the default value of 'Match any' will find a match if any of the criteria are true (ie an "or" link), whereas "Match all" will find a match only if all the specified criteria are true (ie an "and" link).

You can also specify a different column under Use column, so the criteria will be checked against the cells in the column letter you specify here, although the activity will be carried out in the column that the Toolkit is running down.

Ignore empty cells will override any of the other criteria selected by ignoring any empty cell.

2. Cells containing matches all cells containing or not containing a link, error, formula, text, number, duplicate, hyperlink or conditional formatting. Note: This works on the total contents of a cell ie a formula such as '=3*A1' will be treated as a formula (even though it 'contains' a number).

Note: Duplicates and Non-duplicates matches all cells where the text, value or formula as specified of the adjacent cell is the same. There is the option to specify whether to look in both directions or back only (ie whether the first cell in each set of duplicates is to be treated as a duplicate or not).

3. Cells that matches all cells where the text, value or formula is as specified. To specify you have various options, including an 'and/or' option. Text should not have quotes around it.

Within text searches, there is the option for certain characters to represent themselves or to be used as wildcards.

4. Cells with matches the specified font, fill or other property as specified, or works with every row number divisible by n (eg 2 will give all even rows, 3 will give rows 3,6,9,12 etc), or with every n'th row from the start cell.

The latter two selections are particularly useful for colouring in every other row, etc.

For font and fill the window where you make your selection allows you to select either a common matching colour (with its corresponding index number), or a different colour index number (from 1 to 56 inclusive) in the box and press the 'Other' button.

Note: Although you can set the font or fill colour index in the range 1 to 56, the cells may have numbers such as -4105 or -4142 showing using 'Details': these are the values representing 'Automatic' font or 'No Fill'. To select these values use the large 'Reset' button on Tab 5.

Tip: You can hover over or click on "Information" to see the font and fill colour index number for any particular cell.

top

Tab 2

1. Change sign of numbers will simply change the sign of any cells containing a number (eg 2 becomes -2). The numbers themselves will change, rather than adding in the calculation and converting them to formulae.

Cells containing formulae or text will not be altered. So a cell with the formula '=2' will not be changed - to change these, first change the formulae to numbers using the tool below.

2. Move sign of numbers will change numbers imported from other systems such as an AS/400 where the negative sign appears after the number eg 18- becomes -18 using this tool.

Cells containing formulae or positive numbers will not be altered.

3. Convert numbers allows you to add, subtract, multiply or divide numbers, including the option to round the result. The numbers themselves will change, rather than adding in the calculation and converting them to formulae.

Cells containing formulae or text will not be altered. So a cell with the formula '=2' will not be changed - to change these, first change the formulae to numbers using the tool below.

Cell display properties will not be altered either, so rounding the result may not appear as you require until you reformat the cells.

4. Replace digits with words will automatically change the contents of cells from digits (0-9) to words, including negative numbers.

You can also add words or blank out the fields as follows:

Stem suffix - whatever symbol(s) and/or word(s) you put in here will appear before the decimal part
Join - any symbol(s) and/or word(s) to go between the stem above and the decimal part
Decimal suffix - any symbol(s) and/or word(s) to go at the end
So, for example, with the default settings 185.23 becomes One Hundred and Eighty Five Dollars and 23 Cents. If you leave all three fields blank you get One Hundred and Eighty Five 23.

Cells containing formulae will not be altered.

The mechanics for this tool can be downloaded as a free workbook at the PATools website.

5. Combine duplicate numbers is useful when data contains entries that have the same key, and it is necessary to combine these entries by adding together their values in the columns you specify. The numbers themselves will change, rather than adding in the calculation and converting them to formulae

See the tutorial worksheet (PATDTEg.xls) for an example.

You may need to sort your data first so that duplicate entries appear adjacent to each other before running this tool - this tool does not sort your data for you.

This tool retains the data of the first matching row, deleting later rows that match.

6. Convert formulae and numbers will change formulae into their resulting values, or numbers into formulae (ie '2' becomes '=2').

The following cells will be ignored: errors, empty cells and cells that do not evaluate to a number.

7. Change formulae provides quick changing of formulae, for example from sum formulae to subtotal formulae.

top

Tab 3

1. Tidy text gives the following options:

to replace multiple spaces with a single space anywhere in the text (including the start and end);
to remove all spaces at the end of the text (trailing spaces);
to remove all spaces at the start of the text (opening spaces);
to change the case of text to 'Proper Case', 'UPPER CASE' or 'lower case'.
These are not mutually exclusive options and can be run in any combination.

The following cells will be ignored: errors and cells that contain a formula.

2. Remove characters allows you to clear any set of characters from text in a cell. So for example you can clear out all Text characters (a-z and A-Z), all Numbers (0-9), and/or any Others you specify eg / etc.

For Others just type in the list of characters you wish to exclude. However you should note that this is effectively "charlist" as explained in Wildcards and the rules explained there apply. You do not need the square brackets. To match left bracket ([), question mark (?), number sign (#), and asterisk (*), enclose them in brackets.

So, for example, you can extract from text cells just the date as follows: Original text "Peter arrived on 25/12/01 at his home" can become "25/12/01" by excluding text and the space character as Others.

3. Convert text and numbers is especially helpful when numbers have been input as text.

3a. Text to numbers will convert numbers entered as text into numbers on which Excel can perform calculations. There is the option for empty cells and words to be converted into the number zero; otherwise they are ignored. Note: Alignment is not changed.

3b. Numbers to text will convert numbers into text. There is the option for zeros to become empty cells; otherwise they are converted.

The following cells will be ignored: errors and cells that contain a formula.

Tip: To ensure that formulae are converted use the 'Convert formulae to text first' button.

4. Insert text in cells will insert any text you specify into the position you specify. You can select at the start or end of a cell, before a position (eg 3 for the third character), or before or after the first character found that you specify (eg A).

The following cells will be ignored: errors and cells that contain a formula.

5. Convert formulae to text first will ensure that all formulae are changed to the displayed text value.

 

top

Tab 4

These are two of the most powerful tools within the Toolkit.

1. Missing numbers from a list will search a column of data for numbers missing from a sequence and list them in a new column to the right or left.

You can specify the multiple for the sequence (eg 1,2,3,4 has a multiple of 1, whilst 2,4,6,8 has a multiple of 2). If the sequence may include larger gaps, these can be ignored (eg in the sequence 1,2,4,5,101,102,104 you may wish to see only 3 and 103 as missing, and not all the numbers from 6 to 100).

You may also add a column to count the missing numbers found.

2. Differences in two sets of data by aligning the sets can be used to align two sets of data thus highlighting the differences. There is the option to delete data that has an empty key.

To enter the data you can type in named ranges, or the range addresses in the format A3:C100. Alternatively you can use the '...' button to select your ranges on the worksheet.

It is important that both sets of data are on the same worksheet, and that they (the data, excluding any titles or headers) both start on the same row. Any titles or headers for the data should be ignored for this tool, and will be left unaltered.

In each set of data the key (ie the cell to be compared) must be the first column on the left of the data set. Both sets of data will be sorted (ascending) by this key first without the usual prompt to decline, and this will not be counted as a change to your data in the final message.

top

Tab 5

These utilities are added as an extra bonus, and work slightly differently from the rest of the tools.

1. Worksheets gives two tools:

To add worksheets named according to the text in a selection of cells; so for example, you can easily add 12 new worksheets with the names January to December - just type "January" in an empty cell, drag down 11 cells below that, and then use this tool. If your selection contains blank cells then the Excel default names will be used.

To add an index page called "Contents" (if that sheet name does not already exist) with clickable hyperlinks to all the sheets in your workbook.

2. Reset allows instant resetting of font and/or fill for the whole sheet.

Font resets the font colour, and removes bold, italics and underline.

Fill resets the colour.

3. Move allows you quickly to move column headers to become row headers, or vice versa, when creating a table of data. This can be especially useful if these headers are formulae, since cutting and pasting (in transpose mode) will not always adjust the formulae properly.

4. Swap cell contents with block is a tool to allow you quickly to exchange (swap) cell contents with neighbouring cells for any selected block - easier than multiple cutting and pasting.

The contents will be exchanged, but the formats of cells will not.

top

Quick clix (middle section of screen)

"Reset" which resets the last used cell on a sheet - keeps your workbooks down in size and therefore improves efficiency

"Unhide all rows" and "Unhide all columns" - right-click to run on all sheets

"Remove hyperlinks" removes all hyperlinks on a sheet - - right-click to run on all sheets

"Protect / Unprotect" to protect (left-click) or unprotect (right-click) all worksheets in the workbook. NB To unprotect all worksheets that have passwords, the password must be the same on every sheet.

top

Wildcards

The following characters have special meanings:

Run Options

Note: If you right-click on any of the following checkboxes you have the option to change the default setting.

1. Safeguard is an option to protect against user error by interrupting the tool every 500 (by default) cells to allow you to abort or continue. For example, if you (accidentally) start the Toolkit at row 1 on a column of empty cells and tell it to stop at row 50, carrying out the activity of deleting rows for empty cells found, it will never reach row 50. In this case the Safeguard will kick in and allow you to stop.

The default for Safeguard is 500. To change this right-click and enter the new value. You will have the option to set this new value as the default, and to set the default for Safeguard as On or Off.

It is recommended that Safeguard is always switched on, but advanced users working on large volumes of data can reset it to a very large value so as not to get too many unwanted interruptions.

2. Screen off will give even more speed by deactivating the screen while the tool runs so you will not see the alterations being made. This is best used for large worksheets, and you will need to be patient - it is working even if you cannot see it!!!

3. Ignore hidden rows will prevent the tool from reviewing or carrying out any activity if the cell is within a hidden row.

4. Return to start cell will take the cursor back to the start cell at the end (otherwise the cursor remains where the tool finishes).

General Notes, Tips and Precautions

1. Hide temporarily hides the Toolkit to allow you to work on the worksheet, and when reactivated will instantly reopen it in the same screen position with the same selections as when it was hidden. In the meantime you can open and close files, and use all the Excel facilities.

Note: No harm will come if you close down Excel while it is hidden.

2. Although the software is well tested and written, we would recommend as good practice that any data and template workbooks be saved and backed-up before running this software, as it is impossible to protect against every possibility of system crashes, printer problems, etc causing runtime problems that may damage your data.

3. For this, as with any software, the best way to evaluate it and make it work for you is to play with it using dummy data until you become comfortable and are ready to use it for real.