Revised:May 18, 2002

It took about 15 minutes to casually import and make a comparison that displayed all 8 differences between a Macola and a VeriBest BOM using these procedures. For OrCad, follow the VeriBest directions.

These procedures work for BOM output from other applications as well. Several handy Excel techniques are used here.

Notes:

· To select areas using the keyboard, hold down the shift key, press End and then a cursor key, or End then Home (goes to bottom right corner), or press Ctrl+Home (goes to top left corner -- if optons are set to use the Lotus 1-2-3 transistion mode then just press Home).

· To widen or narrow columns click and drag the boundary between the columns in the index at the top of the columns. Double clicking that boundary will auto-size the column unless the cells are all set to wrap.

· To rearrange columns, right click on the column index above the column and choose Cut columns. Right click on another column index and choose Insert_cut_cells.

CONVERT MACOLA

1) Open the Macola BOM as a worksheet.

2) Select the whole BOM and unbold it all.

3) Of any part, bold the first cell that holds a reference designator (use any part that is representative of all parts in the list).

4) Drag to select from the first Seq number (not the header) over to the right and down just past the end of the list.

5) Choose the One-row-per-part action and click Apply. Click No when asked if you are removing redundant text.

- MACOLA HAS BEEN TRANSLATED TO SINGLE ROWS PER PART -

CONVERT VERIBEST/ORCAD

1) Open a new Excel sheet. Open Refinate Format display box. Choose the TextToColumns action and click Apply to reset it.

2) Open the VeriBest/OrCad BOM into a text editor, copy it, and Edit_PasteSpecial_Text into any cell of Excel.

3) While the imported list is highlighted, choose the TextToColumns action and click Apply.

4) Choose Delimited in the TextToColumns dialog that pops up, then click Next.

5) Type the | (bar) for VeriBest into Other and mark that box (for OrCad mark Tab instead) and click Finish.

6) Bold one cell in the ”References” column.

7) Drag to select from the top left Item# down to the bottom right of the entire list.

8) Choose the One-row-per-part action and click Apply. Click No when asked if you are removing redundant text.

- VERIBEST HAS BEEN TRANSLATED TO A COMMON FORMAT -

COLLECT ALL REFDES INTO A SINGLE SUMMARY CELL WHILE CHECKING FOR FORMAT FAULTS

1) In one of the output lists, drag to select all the reference designators in their respective column and click Get/Test.

2) Any format problems will be indicated and the Query dialog will pop up. Use Query to correct the first problem and then close the Query dialog box. Click Get/Test to find and correct any other problems using the same editing technique until there are no format errors (remove any text comments placed in the refdes group). Remove duplicates that are found so you can get a final compilation (a log of the duplicates will be placed in comment boxes).

3) Right-click and paste the results beneath all the reference designators - skip two rows so it’s easy to find and so they don't get included in a sort later.

4) While that pasted cell is selected, choose the Paint-Table-Format action and click Apply to cause it to wrap.

Do this for both the Macola and the VeriBest lists. Breaks in refdes ranges can be seen. Compare the counts.

- MACOLA & VERIBEST HAVE BEEN CLEANED, SUMMARIZED & CHECKED FOR DUPLICATES & MISSING REFDES -

OVERALL COMPARE (COMPARE PART RANGE SUMMARIES - MACOLA VERSUS VERIBEST)

1) Select one of the summary cells you made. Click Get/Test and then SUBTRACT the other list’s summary cell. Any parts still remaining in the display box are parts not contained in the cell that was subtracted. So these remaining parts are only in the BOM of the first cell selected (using Get/Test). Right-click and paste them into a cell below the summary cell that you began with so you can refer to later.

2) Then select the cell that you just subtracted and this time click Get/Test. Go to the other list and SUBTRACT its summary cell. Any parts still remaining in the box are parts not contained in the cell that was subtracted. Paste them below the summary cell that you began with (using Get/Test).

- IF NO PARTS REMAIN IN THE DISPLAY BOX FOR BOTH SUBTRACTIONS THEN BOTH BOMS HAVE THE SAME REFERENCE DESIGNATORS (but they might not be assigned to the same parts) -

PER PART COMPARE (COMPARE INDIVIDUAL PARTS - MACOLA VERSUS VERIBEST)

1) Choose the Paste_Format option in the Refinate Format display. Set the mode to Count Ranges.

2) For both VeriBest and Macola, select the top-most refdes cell and click the button labeled Paste_V to paste downward reformatted refdes. Keep clicking that button if the bottom isn’t reached due to empty cells.

3) If the refdes in that new column are not already formatted to wrap text, then select all the refdes in that new column and use the Paint-Table-Format action to have them wrap. If the column is too narrow, click and drag the boundary between the columns in the index at the top of the columns.

4) For both lists, while selecting at least one cell in the refdes column, use the Excel menu to SORT on that column - Data_Sort_OK (mark proper HeaderRow option, confirm SortBy column, ExpandTheSelection if it asks). Now both lists will be have been sorted based on their per-row reference designators that Refinate had standardized.

5) Copy just the reference designator column from one list and paste it next to the other list of refdes to see the differences (make a note of which list is VeriBest and which is Macola.

Now that the reference designator columns are formatted alike, you might want to use your own familiar comparison techniques (e.g. copy and paste only the refdes columns of each list into separate files and use a file compare utility, or make a printout and work on paper).

· Insert one blank column between the two columns being compared so they are easily discerned from one another.

· Work down the right column only. If the right column of refdes has more rows of parts than the left column, select the first extra part (refdes) cell, right click and Insert_Entire_Row_OK. Then select the blank cell in the newly created row just above the right column that was shifted downward, right click and Delete_OK (ShiftCellsUp). If the right column needs to be shifted down to align with the left column, select the first cell to move downward in right column, right click and Insert_OK (ShiftCellsDown).

· When the two columns appear to be entirely aligned properly at a glance, write an equation at the top between the two columns and copy it down to the bottom.

Start that equation by typing an = and then point to the cell on the left and type another = and then point to the companion cell on the right in the same row and press Enter. Then do a Ctrl+C (copy) and hold the shift key down to highlight while pressing the down cursor to drag down in that column to where the parts stop and do a Ctrl-V (paste). (Alternatively, grab the + in the right corner of the equation cell, drag and release it at the bottom of the column). That copies the equation down to the bottom. To make the FALSE easier to see quickly, narrow the column by dragging the boundary in the column index of the TRUE/FALSE column just until FALSE changes to “*****” since there’s not enough column width to show the whole value.

· After the equations have been placed, if there has to be any further insertion of rows and moving of cells, that comparison equation must be re-copied from a cell above the changes down to the bottom again. Otherwise, the equations follow the cells that were moved instead of comparing on the same row. If in doubt about the equation, click on a cell containing an equation and then click inside Excel's edit box to highlight the cells it refers to (you can drag those highlights to correct the equation).

· When differences between the columns are found, select the cell in the leftmost column first, click Get/Test and SUBTRACT the one on the right. Whatever remains in the display box are extra parts listed in the left column (the one you began with (using Get/Test). Paste that in the same row over to the right. Then select the rightmost cell and SUBTRACT the left. Any parts remaining in the box are extra parts listed in the right column. Paste that on the same row further to the right than the "extras" from the left column.

- ALL OF THE DIFFERENCES BETWEEN ROWS OF REFDES HAVE BEEN IDENTIFIED -

But they still might not have been assigned to the identical parts in both BOMs. So highlight each list and use File_PrintArea_SetPrintArea to restrict the printout to just the translated table (optionally include your notes) and print them out to compare part descriptions. Reminder: Use PrintPreview to set Landscape/Portrait, Fit to 1page Wide or 1page Tall, Center Horizontally, adjust Margins small.

The Macola quantity counts can be verified by choosing Paste_Count. Then use the Paste_V button and place a copy of the Macola QTY column next to the newly created counts for comparison.