——————
——————
 |
Refinate Basics |
THE BASICS
Copy and paste a list into Excel (or open a spreadsheet) and Refinate will
work together with Excel to assist you as you verify, create and modify lists.
Count, find duplicates, log duplicates, log changes, compare, re-arrange,
generate new list versions. There are no requirements for the structure of the
worksheet so Refinate can be used to assist with any kind of worksheet or
imported text. For all operations, you will select an area of interest and then
click an appropriate button to achieve the desired result.
|
Original Lists |
Some basic operations |
Results |
||||
| Qty | Description | RefDes | Count Items | RefDes | RealQty | |
| 3 | SamplePart_1 | J7, J3-J5 | After Refinate Ž | J3-J5, J7 | 4 | |
| Qty | Description | RefDes | Make Ranges | RefDes | RealQty | |
| 3 | SamplePart_1 | J5, J3, J4 | After Refinate Ž | J3-J5 | 3 | |
| Qty | Description | RefDes | Expand Ranges | RefDes | RealQty | |
| 3 | SamplePart_1 | J3-J5 | After Refinate Ž | J3, J4, J5 | 3 | |
| Qty | Description | RefDes | Find Duplicates | |||
| 3 | SamplePart_1 | J3-J5 | After Refinate Ž | duplicate= J4 | ||
| 1 | SamplePart_2 | J4 | ||||
|
To see the two objects that Refinate adds to Excel (a toolbar and a dialog box), go to Screenshots. |
|
To see a before/after view of using the option to convert a BOM, go to One-row-per-part. |
|
To see pictures with descriptions of procedures, go to some of the links listed beneath "Support". |
|
To read about some of the processes that can be performed, go to Examples. |
|
To follow some basic steps to become familiar with Refinate, go to Quick Exercises. |
Be sure to retain a backup file of your original data since the spreadsheet will be modified as you use Refinate's functions.
TWO MODES: COUNT RANGES and IGNORE RANGES
The Count Ranges mode is
typically used for working with indexed items such as reference designators in
bill of materials (BOM). When an operation is performed in the Count Ranges
mode, indexed items must abide by a few rules which allow hyphenated ranges to
be counted properly (e.g. R1-R3, R5; count=4). In this mode, any time a rule is
not satisfied, processing stops and a format fault message is displayed to
indicate the cause of the problem. The fault must be corrected before processing
can run to completion. Sorting and checking for duplicates is always performed
in this mode.
The Ignore Ranges mode is typically used to work with normal
text/numbers which do not contain indexed items. In this mode, the text/numbers
are not tested to see that they follow rules. When an operation is performed in
Ignore Ranges mode, any text is permitted and numbers are treated as any other
text character (e.g. HC1-2, RJ-45, 525B; count=3 since there are only three
items that are comma delimited). This mode can be used for arbitrary text
including reference designators that do not satisfy the rules for Count Ranges
mode. While in Ignore Ranges mode, sorting and checking for duplicates is an
option that can be disabled. This permits cells to be joined without re-ordering
the contents. When Sort/ Duplicates is set, any duplicates found are either
brought to focus on the screen (when GoTo Duplicates is set) or are counted and
displayed in a comment box which is created after the processing is completed.
Since numbers will not be treated any differently than alpha text, the sort
order will be different for Ignore Ranges mode versus Count Ranges mode. For
example; CountRangesSort=C2, C10 but IgnoreRangesSort=C10, C2 since the digit
"1" comes before the digit "2" just like the letter "A" comes before the letter
"B").
Even though you might be working with indexed items (i.e. refdes), there
sometimes can be an advantage to using Ignore Ranges mode. For example, you
might want to create a complete list of all duplicates inside a comment box
instead of having each duplicate displayed one at a time. To create a complete
list of duplicates, if the indexed items are already individually listed without
hyphenated ranges, all that is necessary is to enable Ignore Ranges and turn off
GoTo Duplicates. However, if items are represented as hyphenated ranges (e.g.
R3-12), then first use Auto-Paste with the Expand Ranges option set to create a
new list which shows every indexed item. Then select that newly pasted data and
use the Ignore Ranges mode to create a comment box listing all duplicates found.
IMPORTING TEXT FILES AND PARSING INTO COLUMNS
Copy your list to the clipboard from any application. In Excel, select any cell and use Excel_Edit_PasteSpecial_Text. You can also try pasting HTML format. After your list has been pasted, apply either Refinate’s Parse-Semicolon/Set action or Refinate’s TextToColumns action. In both cases the TextToColumns dialog will open for you to determine the best settings for the cleanest output. If you use the Parse-Semicolon/Set action, some additional parsing aids are available and some adjustments are automated based on the style of the list. In some cases, prior to import and conversion, there could be a benefit to modifying your input file with the "Find & Replace" of a word processor to make line split points easy to find. After parsing the text into columns, you can use Refinate's Gather-1-Delimit/Cell and Refinate’s One-row-per-part action to gather items that span many rows and put them on the same row as the part number (as a record format). The One-row-per-part action can combine cells (such as all refdes) and it can also filter redundant information found in consecutive rows (such as found in some assembly lists).
AUTO-PASTING / MODIFYING / CREATING NEW LISTS
Your original data in the worksheet is
kept intact whenever possible so that it can be referred to when examining the
modifications made during processing. For example, when using the
One-row-per-part action, an entirely new table is created next to the input
information on the same sheet. In this case, the input data might be slightly
modified in the worksheet since spaces are sometimes automatically placed after
commas. Another example is, when using the Auto-Paste function, the results of
process will be placed in the neighboring column to the source data so that the
source and the result can easily be compared before replacing the original data.
Sometimes it is useful to Auto-Paste new cells just to temporarily see some
results before clearing them again. For example, use Auto-Paste to acquire a
column of refreshed counts and then erase them if they are the same as counts
already present in another column.
Some actions provided in the Refinate action list will modify the source data
within the original cells. For example, the Prefix/Set action will find the
first prefix in a cell and then add it to all other delimited numbers in the
same cell if they are missing the prefix. And the Prefix-Clear action will
remove all prefixes from numbers except for the first one in the cell. Another
example is the Number-Values action which will change a cell containing a
text_number into one that contains a number_value so that it can be used with
Excel's math formulas. The Number-Values action can be used to restore numbers
back to values after using the One-row-per-part action.
PRINTING LOGGED NOTES AND TABLE FORMATS
To make a print-out that includes comment tags (the logged notes), use Excel_File_PageSetup_Sheet and then in the Comments drop-down box you can choose to have them not printed, or printed as they appear on screen (be sure your print selection is expanded to include them), or choose to have them all printed together on the last pages of the print-out.
If you use Refinate’s One-row-per-part action, several print-out settings are made automatically. You can use the One-row-per-part action on tables that already use a single row per part just to take advantage of this and other automatic formatting.
Get Refinate for Excel 97 or newer.