Introduction

The Value List Builder Excel file is intended to aid description technicians in building DESClogik templates, which may require extensive searches in the value lists. The Workbook and Macro significantly speed up the value list and template building process.

The related workbook Value List Builder Scientist is intended to help scientists find useful terms that are already in our database, which prevents duplicates and may improve continuity between expeditions. It functions the exact same as the Value List Builder, but the value list directory is to a copy of the value lists placed on a scientist's work station rather than the server.

Apparatus, Reagents, & Materials

• Any Windows computer with Microsoft Excel.

• Excel Workbook Value List Builder and Value List Builder Scientist found in the ALL Things DESClogik folder. A copy exists under the expedition Value Lists folder, attached below, and an originals can be found in the following link; T:\IODP_Share\ALL things DESClogik\Value List Builder

Value List Builder.xlsm

Value List Builder Scientist.xlsm

You must rename these files as captioned above after you open it.

Method Overview

Description scientists will provide the description technician an Excel/Word copy of the values they are expecting to use on the current expedition, and want added to their template. The technician then searches the DESClogik value lists using the command button within the Value_List_Builder workbook. This workbook loops through the folder stored on the IODP server, which should contain all the individual value lists workbooks.
Technicians may wish to furnish the description scientists with a copy of the value lists and the Value List Builder Scientists macro workbook. Both need to be saved to the desktop and placed in a folder named "DESClogik Terms", with a separate folder for value list, labeled "Value Lists".
An Excel User Form window appears once you click the magnifying glass search icon in the ribbon of the Excel sheet. The macro checks values against the value list library with various user customizable parameters. If found, each value is added to the excel sheet with the appropriate references and default DESClogik template settings. If the search results are suitable, the technician will copy the referenced list directly into a DESClogik template configuration workbook or value sublist workbook. If the macro does not find a match, Excel will place the missing values in a separate column on the same sheet labeled Missing Values.

Excel Workbooks and Procedure

The Excel workbook Value List Builder (VLB) and Value List Builder Scientist (VLBS) has several embedded macros. You access these search and clear macros through appropriately related icons in the Excel ribbon and in the user form.

Workbook Tabs

The first tab/sheet of the VLB and VLBS has the name "Paste Your List Here" and this where you paste their list, specifically, in the first column "Paste Your Value List Below." The sheet is pre-labeled with the headers that resulted from the last search, except for the paste column, which the program checks and renames each time you run the macro. You may add other sheets to work and keep track of their search results as needed without effecting the macros. There is a second sheet pre-labeled Free Worksheet for convenience.
The first sheet contains three command icons as follows: the magnifying glass is the search function, the broom icon clears search results, and the red "X" clears the entire form, except the headers. When the user clicks on the magnifying glass icon, an Excel user window opens with several search and clear options, which will perform the actual searches when you click on them. The sheet "Paste Your List Here" and associated command icons are shown in Figure 1 below.

Procedure

The following procedure outlines a typical technician task of adding values found in the value list Excel workbooks.

1. Open Value_List_Builder workbook, paste your values and click the VLB command icon (magnifying glass).

• Obtain the lists of values from each description group.

• Open the VLB workbook from Value List folder.

The VLB's paste sheet with example results is displayed below in Figure 1. You will notice the columns to the far left (Column A) are highlighted in yellow. This is where you will paste the values you want the VLB to search for.


Figure 1. Excel Paste Your List Here sheet with command icons.

• Paste your values into the first column, highlighted in yellow, labeled Paste Your Values List Here. The pasted values do not need to be in any specific order or format. The VLB macro will return search results in the order you entered them. Search All Lists option returns value matches in alphabetical order. Empty cells, leading and trailing spaces, are ignored.

• When you have entered your values, click the magnifying glass icon to begin searching (Figure 2). Excel asks you to enter your expedition number if you are using the VLB, but doesn't for scientists using the VLBS, and will then open the user form Value List Selection (Figure 3). The other icons clear either the search results or the entire form, except the headers.

Figure 2. Command icons. You click the magnifying glass when ready to begin searching. 

2. Input your expedition number and select search parameter options.

After clicking the search icon (magnifying glass) in the VLB you will prompted to input your expedition number. The macros use this number to set the folder directory it will search. The folder directory/file structure should be consistent with other expeditions; otherwise, the macro will give you an error. Below is an example of the proper file structure. Note the underscore before the expedition folder.

T:\IODP_Share\ALL things DESClogik_DESClogik_EXP371\Value Lists

Next, the macro will open a user form shown in Figure 3 below.

Figure 3. Value Lists Selection User Form.

Now you will select your search options.  The user may choose from several options when performing a value search. They are outlined below, and in Figure 4.

Figure 4. Value Lists Search Options.

• Search All Value Lists Option (Callout 1).  The user selects this option by clicking the Search All Value Lists button. The macro loops through all value list workbooks and returns all possible matches found for each of the user's values. The macro also returns the workbook in which it found the value, and sorts these returns alphabetically. This method ignores any categories or value lists selected from the drop down menus. The user may select or deselect the Exact Match Only option as well.

• Standard Search Option (Callout 2).  The user selects this option by choosing an individual value list workbook in which to search, then clicking the Search button. They must first select a category. This method searches only the value list selected by the user. The user may select or deselect the Exact Match Only option as well.

• Fossil Category Search Option (Callout 3).  The user selects this option by choosing the Value List Category, "Fossils" and leaving the Value Lists field empty, then clicking the Search button. The macro will only search through the fossil groups, e.g. benthic forams. The macro also formats the results to make copying and pasting into DESClogik configuration workbooks easy. 

• Exact Match Only and All Possible Matches Option (Callout 4).  The Exact Match Only option will restrict all search returns to exact matches only. Unselected, the search returns the nearest matches allowing for extraneous characters before and after the user value. For example, with the Exact Match Only option deselected the user value radiolarian might return radiolarian or radiolarian marl. This search only returns the first match found. The All Possible Matches option will continue to search through a list and return all matches found within. The exact match feature can be turned on or off.

Result

The results of the searches are placed into the various columns to the right of the user's value list; the macro found values are always placed in column B. A detailed search result is shown in Figure 5. below. Not all search returns have the same format.

Figure 5. Results of a Fossil category search. Values not found are in red header column.

The search return columns are formatted differently for different search options and values. For example, a search for fossils will place genus species in column B and the DESClogik formatted species with its reference in column C. For Search All Value List searches, the macro places all the information associated with the found value in the columns next to B. Since the number of columns and type of information associated with found values vary with different work books, so do the results columns. That is why the column headers are color-coded.

Though the exact placement of found value's associated information columns (i.e. definition or reference) changes, the headers are colored in such a way as to indicate which column is which. Yellow is only used for the user-inputted values (column A). The found values are colored green (Column B). The light blue columns are for found values and their associated information columns. Note that for all the value types, except fossils, the light blue columns appear in the exact order as they are in their respective value list workbooks. Green is for the workbook names of the found values, and lastly, the red column always appears at the far right and is reserved for values the macro was unable to find.

Things of Note

The macro specifically references the names of the "Value List Builder.xlsm" and "Value List Builder Scientist.xlsm" workbooks and the sheet "Paste Your Values Here". DO NOT CHANGE the names of the workbook or the first sheet. If you change these names, you can simply rename them the originals, or replace the workbook with a copy from the other two locations.
The file path is specific and the file structure of your expedition needs to follow the example given below. Note, that there is an underscore before the expedition number.

T:\IODP_Share\ALL things DESClogik_DESClogik_EXP371\Value Lists

Archived Versions

Open

Open