Docstoc

Matching Chemical Change Worksheet

Document Sample
Matching Chemical Change Worksheet Powered By Docstoc
					 Built by: DL Duewer, NIST                                                                                                                             Paid for, in part, by: NIJ
                                                                 STR_MatchSamples <2-Jun-09>
                                          A System for Matching Samples Across Excel Worksheets by Their STR Profiles

                        ***                                                        Status of Report Sheets                                                          Counts
 ReadMe                          Status       Last Called          Sheet                                        Contains                                            Type            #
                                 Clear     2-Jun-09 10:12:41   Data           Comma-delimited STR-profiles for all samples                                                Total     0
                                 Clear     2-Jun-09 10:12:41   RepDetail      Details of 'Best Match' of Unmatcheds to other Unmatched samples                     Exact Match      0
                                 Clear     2-Jun-09 10:12:41   RepSummary     Summary of Two 'Best Match'es to other Unmatched samples                              Unmatched       0
                                 Clear     2-Jun-09 10:12:41   RepProfile     Profiles for Unmatched and its 'Best Match' to other Unmatched samples            Unmatch (able?)     0
  Clear All
                                                                                                                                                               Off-ladder alleles

         2                   Use Loci Contained in
                                Only the initial dataset
 Add Data                       All datasets
         1                      All datasets, if confirmed


                        FALSE    Delete blank (no specified alleles) records                                                                                      Deleted blanks
Check Data              FALSE    Convert all "OR" allelic designations to "no result"                                                                              Deleted 'OR's
                        FALSE    Remove ambiguous alleles ("?")                                                                                                      Deleted '?'s

                             Match
                                Against all samples
Best Match                      Against unmatched samples
         2
                                 Value         Criteria for number of 'Best Match' profiles listed in RepDetail - at least one must be 'checked'!
                        TRUE         15 Maximum number. Default is 10, lower-bound is 3 and upper-bound is all available.
                        FALSE        90 Minimum %Loci:1+ (i.e., the percent of loci that share at least one allele). Default is 100
 Print Detail


Print Summary


 Print Profile



     Exit



 eaf62aea-c045-4b94-902b-603d800b7a8c.xls!ControlPanel                            11/18/2010 6:23 PM                                                                      1 / 38
  Built by: DL Duewer, NIST                                                                                        Paid for, in part, by: NIJ
                                                          STR_MatchSamples <2-Jun-09>
                                     A System for Matching Samples Across Excel Worksheets by Their STR Profiles




EngineeringControl
   MacroGo


  ReVersion


      Clean


      Align
  eaf62aea-c045-4b94-902b-603d800b7a8c.xls!ControlPanel             11/18/2010 6:23 PM                                                2 / 38
Built by: DL Duewer, NIST                                                                                        Paid for, in part, by: NIJ
                                                        STR_MatchSamples <2-Jun-09>
                                   A System for Matching Samples Across Excel Worksheets by Their STR Profiles
    Align




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!ControlPanel             11/18/2010 6:23 PM                                                3 / 38
                              Built by: DL Duewer, NIST                                                                                                                                                          Paid for, in part, by: NIJ
                                                                                                                                   STR_MatchSamples <2-Jun-09>
                                                                                                              A System for Matching Samples Across Excel Worksheets by Their STR Profiles

                                                                                                                                    Welcome to STR_MatchSamples!
STR_MatchSamples was developed by David Duewer, Analytical Chemical Division, NIST,




                                                                                                                                                    Version <2-Jun-09>
 with advice and support from the Human Identity Project, Biochemical Science Division.




                                                                                                      STR_MatchSamples enables identification of Exact Batch and Best Match samples in a list of STR profiles.
                                                                                           The system was designed to compare profiles from electropherograms processed by different software (particularly different "Expert
                                                                                          Systems") and for the same set of samples analyzed at different times, by different analysts, using different multiplexes, etc. It has also
                                                                                                              proven useful for the evaluation of profiles from related individuals, particularly father/son pairs.
                                                                                                             Outputs results include: a list of all samples that have the exactly the same alleles at all specified loci;
                                                                                                                                      for each sample, a list of up-to-10 samples that Best Match;
                                                                                                                                      for each sample, a list of the two most Best Matches;
                                                                                                                                      for each sample, a color-coded map of the differences relative to the Best Match.
                                                                                                                        where Best Match is defined as the largest percentage of shared alleles.
                                                                                           Profiles stored in either the NIST standard and/or compact formats (see STR_ConvertFormats) can be used to create the profile list.
                                                                                              Any number of Excel worksheets can be combined into the profile list ('tho you need a fast computer if the list gets very long…)

                                                                                                                                                          Problems?
                                                                                                                                  This is still a "beta" release: there will be problems!
                                                                                                      Please repeat whatever caused the blowup a couple of times and note down all symptoms and error messages.
                                                                                                                                   Email the description to david.duewer@nist.gov

                                                                                                                                                      Getting Started
                                                                                            STR_MatchSamples is designed to be operated by "pushing buttons." All functions are available on the ControlPanel worksheet.
                                                                                                 The ReadMe worksheet documents the functions, abbreviations and file formats as well as giving a few usage tips.
                                                                                                                           If you haven't used STR_MatchSamples before, please…

                                                                                                                                                           ReadMe
                                                                                                       …but the best way to learn the tool is to 1) download the demo-data workbook and 2) start pushing buttons!

                                                                                                                                                    Get Started!


                              eaf62aea-c045-4b94-902b-603d800b7a8c.xls!Introduction                                                              11/18/2010 6:23 PM                                                                 4 / 38
Built by: DL Duewer, NIST                                                                                                                                   Paid for, in part, by: NIJ
                                                            STR_MatchSamples <2-Jun-09>
                                          A System for Matching Samples Across Excel Worksheets by Their STR Profiles




                                                                                         History
                                     1-Dec-06                   The Human Identity Project team at NIST has used, with varying degrees of ease and success, a number of
                                                                special-purpose format conversion systems over the past few years. Responding to requests from the forensic
                                                                human identity community for access to these utilities, this (hopefully) more robust and user-friendly system was
                                                                cobbled together from these "bits and pieces" of purpose-built code in later November of 2006.


                                    20-Dec-06                   Beta version released on an unsuspecting world.

                                     1-Jan-07                   Thanks to Becky Hill (NIST's HIP team) and Scott Nagy (Cal DOJ), several "user-abuser" issues were
                                                                addressed, mostly by changes to the Introduction and ReadMe worksheets.

                                    12-Jun-07                   Thanks to Tom Heylen (DNA Laboratory of the National Institute of Criminalistics and Criminology, Brussels
                                                                Belgium), the system can now be used with Excel versions that use "," rather than "." as the decimal point and
                                                                ";" rather than "," as the list separator. Also, a facility that helps ensure use of locally-valid date and time formats
                                                                was added.

                                    18-Jun-07                   Thanks to Becky Hill for recognizing a major bug in tracking loci across datasets. Previous versions only worked
                                                                correctly when the loci in different datasets were sorted in the same order as the first loaded dataset. Sorry
                                                                'bout that! At Tom Heylen suggestion, you can now change the color-coding in the RepProfile (see the bottom
                                                                of the ReadMe worksheet.) Also at Tom's suggestion, you can also chose whether you want "new loci" (i.e.,
                                                                those not specified in the initial dataset) to be Added to the dataset.


                                    19-Jun-07                   A "list separator" error in the new color-coding routine, reported cogently by Tom Heylen, was fixed along with a
                                                                few cosmetic bugs. The "Get Started" button on the ReadMe sheet was moved to the top of the sheet and
                                                                above the freeze-pane.

                                    17-Apr-08                   Thanks to Tom Reid (DNA Diagnostics Center), you can now specify the number of "best Match" profiles listed
                                                                in the RepDetail sheet and/or the minimum % of loci than must have at least one matching allele. While I was at
                                                                it, I cleaned up some stylistc issues in the code.

                             28-May to 1-Jun-2009               Thanks to Jasper Humbert, South Carolina Law Enforcement Division DNA lab, for helping me identify a major
                                                                error: in previous versions, five or more alleles at any locus could cause the generation of really odd and
                                                                potentially wrong output. Also, at his suggestion several data-check options were addded to make life a bit
                                                                simpler.

eaf62aea-c045-4b94-902b-603d800b7a8c.xls!Introduction                       11/18/2010 6:23 PM                                                                                 5 / 38
  Built by: DL Duewer, NIST                                                                                                                                                                                                                                                Paid for, in part, by: NIJ
                                                                                                                 STR_MatchSamples <2-Jun-09>
                                                                                               A System for Matching Samples Across Excel Worksheets by Their STR Profiles


                                                       What is a 'Best Match'?                                                                                                                       ControlPanel Summary Information
For a given "Match From" profile, the STR_MatchSamples system selects the Best Match "Match To" profiles as:                                     Columns A and B of the ControlPanel worksheet contain the command buttons and the radio-button that specify various user-dettable options.
  Those "Match To" profiles with the largest percentage of loci that share at least one well-defined allele with the "Match From" profile.
  Given the same % matching loci, those "Match To" profiles with the largest percentage of alleles shared with "Match From".                     Columns D to G provide information on the progress of the analysis.
                                                                                                                                                  Status: the current condition of the 'Report' worksheets. The status of the Data worksheet is the most important.
In the absence of ambiguous/missing alleles, if A is the Best Match to B then B" will be the Best Match to A.                                      The Data states are: Clear, Loading, Loaded, Checking and Checked. Data must first be successfully loaded, then checked.
However, this symmetry may not hold when one or both of the profiles has ambiguous/missing alleles.                                                The RepDetail, RepSummary and RepProfile states are: Clear, Processing and Done!
                                                                                                                                                  Last Called: The date-time when the last operation occurred that affected the status of the worksheet.
                                                                                                                                                  Sheet: The name of the worksheet
                                                      ControlPanel Commands                                                                       Contains: A short description of the eventual content of the worksheet.
All of the (interesting) STR_MatchSamples commands are accessed from the ControlPanel worksheet.
                                                                                                                                                 Columns I and J list the number of various categories of sample listed in the Data worksheet.
There are currently six commands, each accessed via it's own Command Button:                                                                      Type: The match-type of the samples. The four types (well, the total plus two distinct types plus a subtype) are:
ReadMe: Activates this ReadMe "Instruction Manual" worksheet.                                                                                      Total - the total number of profiles listed in the Data worksheet.
Clear: Clears all status reports, working data, and report worksheets.                                                                             ExactMatch - the number of unique profiles that were shared by two or more of the originally loaded profiles.
Add Data: Allows user to specify a particular worksheet and add it to the list of samples-to-match on Data. Shortcut: <Ctrl-A>                     Unmatched - the number of unique profiles in the originally loaded data that were not shared.
  What data is added depends on the setting of the radio buttons in the "Use Loci Contained In" group, to the right of the Add Data button.        Unmatch (able?) - the number of 'Unmatched' profiles that do not share a sample name with at least one other sample.
   If "Only the initial dataset" is active, then only those loci specified in the initial Add will be used… What's more, only datasets that have  #: The number of samples of each type.
       those loci can be added. If one or more loci are missing, the system will notify you of the situation and stop; excess loci are ignored.
   If "All datasets" is active, then all loci specified in any added dataset will be automatically used.                                         Columns L to O document the worksheets that have been successfully used to load profiles into the Data worksheet.
   If "All datasets, if confirmed" is active, then you will be asked whether a "new-to-the-current-data" locus should be used.                    Rows: The number of rows(i.e., new samples) added from the given dataset.
Check Data: Insures that all profiles on worksheet Data are in standard format and have a unique identifying code. Shortcut: <Ctrl-C>             Date Loaded: the datetime when the dataset was added.
  It also identifies samples that have the same profile and creates a composite identifying code for the unique profile.                          Worksheet: the worksheet from which the dataset was loaded.
Best Match: Generates the three Best Match reports (see below).                                                                                   Directory: the directory holding the worksheet from which the dataset was loaded.
  Depending on the setting of the radio buttons to the immediate right of the Best Match Command Button,                                          #Loci: the number of loci specified in the dataset.
   the three Best Match reports match all of the "UnMatched" (I.e., those that were not found to be "Exact Matches")                              Loci...: the names of all the loci in the dataset.
   either to all profiles or just to the other "Unmatched" profiles.
Exit: Gracefully exits the STR_MatchSamples system.


                                                                                                               Ill-defined Alleles / Genotypes and How to Correct Them
To validly compare profiles, all profiles should be as complete as possible and every non-missing genotype unambiguously specified. While "OL" is accepted as the code for an off-ladder allele, an "OL" allele in one system may be a ">x" in another. It is up to you to resolve such differences.
If you were paying attention in STR_ConvertFormats, you corrected all ill-defined designations before you stored the converted profiles. However, do not despair: ambiguities can be resolved without leaving this system.

STR_MatchSamples regards any allele that contains any non-numeric character other than "L", "O", "X", "Y" or "?" to be ill-defined. The most common ill-defined designations are off-ladder designations such as "<" or ">" and hybrid designations from FSSi-format
                                                                                                                                                                                                                                                                            yellow,
data when the "Designation" alleles and the i-STRess genotype did not exactly agree (hybrid designations list all the possible alleles separated by the four characters " or ".) When Check Data encounteres anything it doesn't recognize, it (1) paints the cell's background
(2) selects the cell, (3) complains about it to you and (4) stops. The background of possibly modified desigtnations is painted blue and you are asked to resolve the issue; the background of "OL" (off-ladder) alleles is painted green and you will notified of their presence.

If you know that there are only a very few ill-defined data, your best choice is probably to (1) edit the contents of the selected cell (or just enter the correct value), (2) activate the ControlPanel worksheet and (3) re-click Check Data. Repeat until the complaints stop.
The shortcut key <Ctrl-C> (that is, the combination of the Control, Shift key and C keys) allows you to skip step (2).

When there are many ill-defined data of similar kind then it may well be more efficient to use Excel's Replace… function; this function is in the Edit menu and can be invoked with the <Ctrl-h> shortcut.
With this you can replace multiple occurances of a given string of characters throughout a selected range of cells or, if only one cell is selected or the entire sheet is selected, thoughout the entire worksheet.
If you are very confident, you can use Replace…'s Replace All command to do everthing all-at-obce but its safer to step through one-at-a-time using its Replace command. Typical use would be replacement of the hybrid construct "0 or " with a null (nothing in the box) or space (" ").
After you correct everything that you know needs correcting, activate the ControlPanel and re-click Check Data (or <Ctrl-C>). Repeat until the complaints stop. Or you become completely frustrated, whichever comes first.

Of course, figuring out what the correct alleles / genotypes should be may be a bit of a puzzle… that's why you shoulda done it in STR_ConvertFormats when you had the pre-converted data to look at. When in doubt, replace ill-defined alleles with the "?" missing data flag.


                                                         The Data Worksheet
The Data worksheet contains all of the loaded data. It has three structural elements:                                                                            A               B           C          D            E               F           …
  Title (cell A1): this lists all of the worksheets loaded via Add Data.                                                                          1   [TEST_012006.xls]FSSI
  SampleCode (columns A and B): the "Type" and "Description" of the unique profiles.                                                              2
     The initial sample "Description" is a combination of the SampleCode as listed in the originating worksheet and                               3               SampleCode                               Loci
       that worksheet's name, separated by a ":". A unique identity code is assigned to all samples from the same                                 4            Type        Description             D8S1179    D21S11               D7S820
       worksheet with different profiles but identical names.                                                                                     5          Unmatched    AF01C:FSSI               13       ?                ?
     There are three possible "Types": ExactMatch, Unmatched and Unmatch (able?).                                                                 6          Unmatched    AF02C:FSSI               11,13    28,29            8,9
       All samples start as "Unmatched". If the first part of the "Description" (that is, the SampleCode) is not shared by                        …
           at least one other unique profile, the sample will be given the Type "Unmatched (able)?"
       If two or more samples share a profile, they will be replaced by a single, unique profile with a "Description" that is a
           composite of all the initial "Descriptions" and the Type will be "ExactMatch".
  Loci (columns D to ?): the alleles present at each locus in compact, comma-separated format.

Note: if you are attempting to find the "exact match" profiles for two or more evaluations of the same set of samples,
this Data worksheet report may be all you need. It will find exactly matching profiles and SampleCodes and will clearly
identify samples that exactly match neither profile nor SampleCode.




  eaf62aea-c045-4b94-902b-603d800b7a8c.xls!ReadMe                                                                                       11/18/2010 6:23 PM                                                                                                                                    6 / 38
  Built by: DL Duewer, NIST                                                                                                                                                                                                                                     Paid for, in part, by: NIJ
                                                                                                                 STR_MatchSamples <2-Jun-09>
                                                                                               A System for Matching Samples Across Excel Worksheets by Their STR Profiles

                                                      The RepDetail Worksheet
The RepDetail worksheet reports the 10 Best Match profiles to all or every Unmatched sample.                                                            A               B        C       D      E          …
                                                                                                                                              1 Details of 'Best Match' of Unmatcheds to other Unmatched samples
The Title (cell A1) is the only "global" element. The remainder of the report consists of individual sample matches,                          2
  formatted in groups of three. The format elements for each sample are:                                                                      3   Sample From        Sample To %Allele %Loci:1+      Sample From
  Sample From (first column of each repeat): the "Description" of the sample being matched. The number of unique,                             4       AF01C:FSSI C25B:FSSI          57       63       AF02C:FSSI
     fully specified alleles in the profile is also listed ("# Alleles") as is the number of loci with at least one fully                     5        #Alleles: 21 C05C:FSSI       52       63        #Alleles: 24
     specified allele ("#Loci").                                                                                                              6          #Loci: 16 C11C:FSSI        52       63          #Loci: 16
  Sample To (second column of each repeat): the "Description" of the matched sample.                                                          7                      C15C:FSSI      52       63
  %Allele (third column of each repeat): the percent of alleles in common between the "From" and "To" samples.                                8                      C17C:FSSI      52       63
  %Loci:1+ (fourth column of each repeat): the percent of loci common to both samples that share at least one                                 9                      C01C:FSSI      48       63
     fully specified allele.                                                                                                                 10                     AF97B:FSSI      52       56
                                                                                                                                             11                      C97B:FSSI      52       56
The ten matched samples are sorted in order of decreasing %Loci:1+, then by %Allele.                                                         12                     AF11C:FSSI      48       56
Ambiguous or undetermined samples (those represented as "?" in the profile) are ignored in this analysis.                                    13                      C07C:FSSI      48       56
                                                                                                                                             14
This report may be of some use when evaluating profiles from related individuals. However, it's primary utility is as the                    15       AF04C:FSSI C04C:FSSI          69     100        AF05C:FSSI
source of data for the following two reports.                                                                                                …


                                                    The RepSummary Worksheet
The RepSummary worksheet reports the best two Best Match profiles to all or every Unmatched sample,                                                  A            B        C      D          E       F          G                              H            I                J               K
                                                                                                                                             1 Summary of Two 'Best Match'es to other Unmatched samples
The Title (cell A1) is the only "global" element. The remainder of the report consists of individual sample matches,                         2
  one sample per row. The format elements for each sample are:                                                                               3    Sample       #Alleles   #Loci         Best Match %Allele    %Loci:1+                                Next Best         %Allele   %Loci:1+
  Sample From (Column A): the "Description" of the sample being matched.                                                                     4      C11C:FSSI          28    16         AF11C:FSSI       75.0   100.0                               AF12C:FSSI               53.6    81.3
  # Alleles (column B) is the number of fully specified alleles in the profile.                                                              5     AF07C:FSSI          27    16          C07C:FSSI       70.4   100.0                               AF82B:FSSI               55.6    81.3
  # Loci (column C) is the number of loci with at least one fully specified allele.                                                          6      C12C:FSSI          27    16         AF12C:FSSI       70.4   100.0                               AF20C:FSSI               59.3    87.5
  Best Match To (column E): the "Description" of the best Best Match sample.                                                                 7     AF11C:FSSI          30    16          C11C:FSSI       70.0   100.0                                C16C:FSSI               53.3    81.3
  %Allele (column F): the percent of alleles in common between the two samples.                                                              8     AF03C:FSSI          26    16          C03C:FSSI       69.2   100.0                                C09C:FSSI               50.0    68.8
  %Loci:1+ (column G): the percent of loci common to both samples that share at least one fully specified allele.                            …
  Next Best Match To (column I): the "Description" of the next-to-the-best Best Match sample.
  %Allele (column J): the percent of alleles in common between the two samples.
  %Loci:1+ (column K): the percent of loci common to both samples that share at least one fully specified allele.


                                                      The RepProfile Worksheet
The RepProfile worksheet provides a detailed analysis of the differences between every Unmatched sample and its Best Match.                              A                B           C         D            E               F          G      H            I                J               K
                                                                                                                                             1   Profiles for Unmatched and its 'Best Match' to other Unmatched samples
The Title (cell A1) is the only "global" element. The remainder of the report consists of pairs of profiles that are                         2
  each other's Best Match. A blank line separates each sample pair. The format elements for each pair are:                                   3                                                                                         Loci
  Type (Column A): the "Type" of the samples (see Data, above).                                                                              4        Type         Description              D8S1179   D21S11              D7S820   CSF1PO D3S1358 TH01                D13S317          D16S539
  Description (Column A): the "Description" of the samples (see Data, above).                                                                5   UnMatched (able?) AF01C:FSSI               13      ?                ?             ?        16,17 6,9                11                11
  Loci (columns D on) contain the compact-format profiles of the paired samples.                                                             6   UnMatched (able?) C25B:FSSI                13,16   30.2,32          10            ?        15,17 6,9                12                9,10
    The profiles are color-coded according to how well they match.                                                                           7
       a) Loci with exactly the same, fully-specified alleles,                                                                               8   UnMatched (able?) AF02C:FSSI               11,13      28,29         8,9           11       14,15     9.3            11                9,12
       b) Loci with ill-defined alleles,                                                                                                     9   UnMatched (able?) C02C:FSSI                11,13      29,31.2       9,12          11       15,18     9.3            10,11             10,12
       c) Loci with fully-specified alleles and that share at least one allele but not all alleles and                                       …
       d) Loci with fully-specified alleles but that share no allele.


Want to change the color codes used in the RepProfile worksheet?                            Change Colors

Just select each of the above profile description cells, row 116 column 2 to row 119 column 2 (I.e, the ones starting with "a)" to "d)") and use the Fill Color tool to set the background color to your desire.
Alternatively, after selecting the desired cell use the formatting tool on the Patterns tab of the Cells… command under the Format menu - or right-click and use the tool on the Patterns tab of the Format Cells command.
After you have modified one or more of the cells, click the Change Colors button. The chosen colors will be applied to the example (to the right of the description cells) so that you may review your choices.
If you don't like the effect, change the colors and click the Change Colors button once again. Stop when you are happy with the colors.

                                                                                                                                The Other Worksheets
The ControlPanel provides access to the various command, displays the current status of the analysis and lists the profile worksheets that have been loaded.

The Introduction and ReadMe worksheets provide some guidance into the use of this system (but you obviously already have already figured that out...)

There are also three normally hidden worksheets: Macro which holds the Excel Macro code which does the analyses, Work which is used for a number of transient calculations and DateTime which is used to set the date and time display formats.
The first column of Work also lists the alphanumeric characters that can be present in a "valid" allele specification. If you want/need to view any of these worksheets, you can 'unhide' them using Format > Sheet > Unhide > sheetname.




  eaf62aea-c045-4b94-902b-603d800b7a8c.xls!ReadMe                                                                                  11/18/2010 6:23 PM                                                                                                                              7 / 38
Built by: DL Duewer, NIST
                                                                                                                      STR_MatchSamples <Version 2-Jun-09>                                                                                                                               Paid for, in part, by: NIJ

                                                                                                        A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                 AutoCall_Globals                                      Parameter Value               AutoCall_Exit              AutoCall_StartUp            SMS_Cancel                  SMS_ChangeColors          SMS_CheckCutoffs             SMS_CheckData               SMS_Clear
                                                                      R1C1 Auto_Open
                 ! In the normal course of events, this is ONLY run by the mode TRUEVBA Macro ! Restore baseline system just priorthe quiting
                                                                                                                                ! Set all to constants                                  ! Allow                   ! Allow user to change       ! Check the structure and all Clear the decks
                                                                                                                                                            ! Take care of User interruptions user to change the RepProfile color code the RepDetail Cutoffs               ! of the profiles
                                                                Calculation mode 1                   =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =ACTIVATE(S0con)
                 ! If you diddle any of the constants, make sure you re-run it yourself before you evaluate the diddle          =ECHO(FALSE)                                                                      =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&
                                                                                                                                                                                        =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                 =SET.NAME("Asyst","STR_MatchSamples")             Start datetime 39966.4253324074 =ECHO(FALSE)                                                                                                   =ECHO(FALSE)
                                                                                                                                                            =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+3,FALSE))         =ECHO(FALSE)                =ECHO(FALSE)
                 =SET.NAME("Avers","2-Jun-09")                      Stop datetime 0.000165277779160533                                                      =COLUMN.WIDTH(0.5)
                                                                                                                                ! Check that the language dependencies are set          ! Ensure ReadMe is active =ACTIVATE(S0con)
                 =MESSAGE(FALSE)                                             User                                               =SET.NAME("NumNew",0) =COLUMN.WIDTH(,,,3)
                                                                                                     ! Restore calculation and RC states                                                =SET.NAME("ClrRow",107)                                ! Tell user                 ! Clear the Data worksheet
                 =ECHO(FALSE)                                 Number invocations                                                =RUN(Utility_DateTimeDependencies,FALSE)
                                                                                                                                                            =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                     =SET.NAME("Atmp1",DEREF(INDIRECT("'"&S0mac&"'!"&Arow&2&Acol&3,FALSE))) =ACTIVATE(S0rme)                      ! Get Cutoffs                =ACTIVATE(S0con)            =ACTIVATE(S0dat)
                                                                                                     =IF(ISERROR(Atmp1),SET.NAME("Atmp1",TRUE))             =ECHO(TRUE)                                           =SET.NAME("LkepMax",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx1row&Acol&2,FALSE)))
                                                                                                                                                                                        =FORMULA.GOTO(Arow&ClrRow-1&Acol&1,TRUE)                                           =SET.NAME("Atitle","")
                                                                                                                                                                                                                                               =FORMULA("Checking...",INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col,FA
                 ! Make sheetnames                                                                   =A1.R1C1(NOT(Atmp1))                                   =ALERT("You have interrupted "&Asyst&"."&Achr&"The message line lists the system's current status."&Achr&Achr&"Go do what you need to do and try again.",2)
                                                                                                                                ! Get the RepProfile color codes                                                                               =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,F
                                                                                                                                                                                                                  =IF(NOT(ISLOGICAL(LkepMax)),SET.NAME("LkepMax",TRUE))    =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&1&Asep&
                 =SET.NAME("Dir0",GET.DOCUMENT(2))                                                                              =RUN(SMS_ChangeColors,FALSE)=HALT()                                                                            =COLUMN.WIDTH(,,,3)         =EDIT.DELETE()
                                                                                                     =SET.NAME("Atmp2",DEREF(INDIRECT("'"&S0mac&"'!"&Arow&3&Acol&3,FALSE))) ! Get the four color codes =SET.NAME("LkepMin",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx2row&Acol&2,FALSE)))
                 =SET.NAME("WB0",GET.DOCUMENT(88))                                                                              =ACTIVATE(S0con)
                                                                                                     =IF(ISERROR(Atmp2),SET.NAME("Atmp2",3))                                                                      =IF(NOT(ISLOGICAL(LkepMin)),SET.NAME("LkepMin",FALSE))
                                                                                                                                                                                                                                               =ECHO(TRUE)                 =COLUMN.WIDTH(0.5)
                                                                                                                                                                                        =SET.NAME("ClrCde1",GET.CELL(63,INDIRECT("'"&S0rme&"'!"&Arow&ClrRow+9&Acol&2,FALSE)))
                 =SET.NAME("S0con","["&WB0&"]ControlPanel")                                          =OPTIONS.CALCULATION(Atmp2)=FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                 =IF(NOT(OR(LkepMax,LkepMin)),SET.NAME("LkepMax",TRUE))
                                                                                                                                                                                        =IF(ISERROR(ClrCde1*1),SET.NAME("ClrCde1",0))                                      =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                                                                                                                               =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                 =SET.NAME("S0dat","["&WB0&"]Data")                                                  =OPTIONS.VIEW(TRUE,TRUE)   =FORMULA.GOTO(Arow&Cbut1row&Acol&1,FALSE)                                         =FORMULA(LkepMax,INDIRECT("'"&S0con&"'!"&Arow&Ckbx1row&Acol&2,FALSE))
                                                                                                                                                                                                                                               =FORMULA.GOTO(Arow&Cbut3row&Acol&1,FALSE)
                                                                                                                                                                                        =IF(OR(ClrCde1<-0.5,ClrCde1>56.5),SET.NAME("ClrCde1",0))
                 =SET.NAME("S0dti","["&WB0&"]DateTime")                                              =SET.NAME("Atmp1",NOW())                                                                                     =FORMULA(LkepMin,INDIRECT("'"&S0con&"'!"&Arow&Ckbx2row&Acol&2,FALSE))
                                                                                                                                                                                                                                               =ECHO(FALSE)                ! Clear the worksheet
                                                                                                                                                                                        =SELECT("("&Arow&ClrRow+9&Acol&2&Alsp&Arow&ClrRow+6&Acol&13&Asep&Arow&ClrRow+7&Acol&13&Alsp&Arow&ClrRow+9&Acol&8
                 =SET.NAME("S0int","["&WB0&"]Introduction")                                                                     ! Set the program identifiers
                                                                                                     =FORMULA(Atmp1,INDIRECT("'"&S0mac&"'!"&Arow&5&Acol&3,FALSE))                       =IF(ClrCde1<0.5,PATTERNS(0),PATTERNS(1,,ClrCde1,TRUE))                             =ACTIVATE(S0wrk)
                 =SET.NAME("S0mac","["&WB0&"]MS_Macro")                                                                         =SET.NAME("Aprog",Asyst&" <"&TEXT(Adate,AlngDat)&">")
                                                                                                     =SET.NAME("Atmp2",DEREF(INDIRECT("'"&S0mac&"'!"&Arow&4&Acol&3,FALSE)))                                       ! Check the maximum number   ! Initialize data display   =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&2&Asep
                 =SET.NAME("S0rme","["&WB0&"]ReadMe")                     AlngDat d-mmm-yy           =SET.NAME("Atmp3",24*(Atmp1-Atmp2))                                                                          =SET.NAME("NkepMax",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx1row&Acol&4,FALSE)))
                                                                                                                                                                                                                                               =ACTIVATE(S0dat)            =CLEAR(3)
                                                                                                                                                                                        =SET.NAME("ClrCde2",GET.CELL(63,INDIRECT("'"&S0rme&"'!"&Arow&ClrRow+10&Acol&2,FALSE)))
                 =SET.NAME("S0wrk","["&WB0&"]Work")                       AlngTim hh:mm:ss                                                                                              =IF(ISERROR(ClrCde2*1),SET.NAME("ClrCde2",0))
                                                                                                                                =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" Welcome to "&Asyst&"!")                                                 =SELECT("("&Arow&5&Acol&1&Asep&Arow&65536&Acol&1&Alsp&Acol&256&")"
                                                                                                                                                                                                                  =IF(ISERROR(NkepMax),SET.NAME("NkepMax",0))
                 =SET.NAME("S0rp1","["&WB0&"]RepDetail")              AlngDatTim d-mmm-yy hh:mm:ss! Restore screensize          =RETURN()                                                                         =IF(NkepMax<0.5,SET.NAME("NkepMax",10))
                                                                                                                                                                                                                                               =CLEAR(3)
                                                                                                                                                                                        =IF(OR(ClrCde2<-0.5,ClrCde2>56.5),SET.NAME("ClrCde2",0))                           ! Format the control panel
                 =SET.NAME("S0rp2","["&WB0&"]RepSummary")                     Acol C                 =FULL.SCREEN(FALSE)                                                                                          =FORMULA(NkepMax,INDIRECT("'"&S0con&"'!"&Arow&Ckbx1row&Acol&4,FALSE))
                                                                                                                                                                                                                                               =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                                                                                                                                                                                                           =ACTIVATE(S0con)
                                                                                                                                                                                        =SELECT("("&Arow&ClrRow+10&Acol&2&Alsp&Arow&ClrRow+6&Acol&9&Asep&Arow&ClrRow+7&Acol&11&")")
                 =SET.NAME("S0rp3","["&WB0&"]RepProfile")                    Adcp .                  =WINDOW.MAXIMIZE()                                                                 =IF(ClrCde2<0.5,PATTERNS(0),PATTERNS(1,,ClrCde2,TRUE)) =PATTERNS(0)                =SELECT(INDIRECT("'"&S0con&"'!"&Arow&1&Acol
                                                                             Agnl General                                                                                                                         ! Check the minimum %loci=COLUMN.WIDTH(0.5)              =CLEAR(1)
                 ! Set the display text for the Report worksheets             Alsp ,                 ! Exit nicely                                                                                                =SET.NAME("PkepMin",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx2row&Acol&4,FALSE)))
                                                                                                                                                                                                                                               =COLUMN.WIDTH(,,,3)         =FONT.PROPERTIES("Arial","Regular",10,,,,,,,1)
                                                                                                                                                                                        =SET.NAME("ClrCde3",GET.CELL(63,INDIRECT("'"&S0rme&"'!"&Arow&ClrRow+11&Acol&2,FALSE)))
                 =SET.NAME("Nrepsht",3)                                      Arow R                  =ACTIVATE(S0con)                                                                                             =IF(ISERROR(PkepMin),SET.NAME("PkepMin",0))
                                                                                                                                                                                        =IF(ISERROR(ClrCde3*1),SET.NAME("ClrCde3",0))          =FREEZE.PANES(FALSE) =FORMAT.NUMBER("0")
                                                                         NumNew
                 =SET.NAME("S0rp1_txt","Details of 'Best Match' of Unmatcheds") 0                    =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                                            =IF(OR(PkepMin<0.5,PkepMin>100),SET.NAME("PkepMin",100))
                                                                                                                                                                                                                                               =FREEZE.PANES(TRUE,3,4) ALIGNMENT(3,FALSE,3,0)
                                                                                                                                                                                        =IF(OR(ClrCde3<-0.5,ClrCde3>56.5),SET.NAME("ClrCde3",0))                           =
                 =SET.NAME("S0rp2_txt","Summary of Two 'Best Match'es")                              =ECHO(TRUE)                                                                                                  =FORMULA(PkepMin,INDIRECT("'"&S0con&"'!"&Arow&Ckbx2row&Acol&4,FALSE))
                                                                                                                                                                                                                                                                           =SELECT(INDIRECT("'"&S0con&"'!"&Arow&2&Acol
                                                                                                                                                                                        =SELECT("("&Arow&ClrRow+11&Acol&2&Alsp&Arow&ClrRow+6&Acol&8&Asep&Arow&ClrRow+7&Acol&8&Alsp&Arow&ClrRow+6&Acol&12
                 =SET.NAME("S0rp3_txt","Profiles for Unmatched and its 'Best Match'")                =FORMULA.GOTO(Arow&Cbut8row&Acol&1,FALSE)                                                                                                 ! Get
                                                                                                                                                                                        =IF(ClrCde3<0.5,PATTERNS(0),PATTERNS(1,,ClrCde3,TRUE))maxium rows&cols =FORMAT.NUMBER(AlngDatTim)
                 =SET.NAME("Amat1_txt"," to all samples")                                            =RETURN()                                                                                                    ! Get Options                                            =ALIGNMENT(4,FALSE,3,0)
                                                                                                                                                                                                                                               =SET.NAME("RowLst",GET.DOCUMENT(10))
                 =SET.NAME("Amat2_txt"," to other Unmatched samples")                                                                                                                                             =SET.NAME("LOptBlk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx3row&Acol&2,FALSE)))
                                                                                                                                                                                                                                               =SET.NAME("ColLst",GET.DOCUMENT(12))
                                                                                                                                                                                                                                                                           =SELECT(INDIRECT("'"&S0con&"'!"&Arow&3&Acol
                                                                                                                                                                                        =SET.NAME("ClrCde4",GET.CELL(63,INDIRECT("'"&S0rme&"'!"&Arow&ClrRow+12&Acol&2,FALSE)))
                                                                                                                                                                                                                  =IF(NOT(ISLOGICAL(LOptBlk)),SET.NAME("LOptBlk",FALSE))
                                                                                                                                                                                        =IF(ISERROR(ClrCde4*1),SET.NAME("ClrCde4",0))          =IF(RowLst<5.5)             =ALIGNMENT(2,FALSE,3,0)
                 ! Country-Version dependencies that can be automatically determined                                                                                                                              =SET.NAME("LOptBlk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx3row&Acol&2,FALSE))) error... must
                                                                                                                                                                                                                                               = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data
                                                                                                                                                                                        =IF(OR(ClrCde4<-0.5,ClrCde4>56.5),SET.NAME("ClrCde4",0))                           =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched2c
                 =SET.NAME("Acol",INDEX(GET.WORKSPACE(37),7))                                                                                                                                                     =SET.NAME("LOptAor",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx4row&Acol&2,FALSE)))
                                                                                                                                                                                                                                               = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                                                                                                                                                           =ALIGNMENT(4,FALSE,3,0)
                                                                                                                                                                                        =SELECT("("&Arow&ClrRow+12&Acol&2&Alsp&Arow&ClrRow+6&Acol&14&Asep&Arow&ClrRow+7&Acol&15&")")
                 =SET.NAME("Adcp",INDEX(GET.WORKSPACE(37),3))                                                                                                                                                     =IF(NOT(ISLOGICAL(LOptAor)),SET.NAME("LOptAor",FALSE))
                                                                                                                                                                                        =IF(ClrCde4<0.5,PATTERNS(0),PATTERNS(1,,ClrCde4,TRUE)) = ECHO(TRUE)                =SELECT(INDIRECT("'"&S0con&"'!"&Arow&2&Acol
                 =SET.NAME("Agnl",INDEX(GET.WORKSPACE(37),26))                                                                                                                                                                                 = ALERT("There must be at least four header rows and at least two
                                                                                                                                                                                                                                                                           =ALIGNMENT(3,FALSE,3,0)
                                                                                                                                                                                                                  =SET.NAME("LOptAor",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx4row&Acol&2,FALSE))) sample rows
                 =SET.NAME("Alsp",INDEX(GET.WORKSPACE(37),5))                                                                                                                                                     =SET.NAME("LOptQmk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx5row&Acol&2,FALSE)))
                                                                                                                                                                                        =FORMULA.GOTO(Arow&ClrRow-1&Acol&1,TRUE)               = HALT()                    =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched2c
                 =SET.NAME("Arow",INDEX(GET.WORKSPACE(37),6))                                                                                                                                                     =IF(NOT(ISLOGICAL(LOptQmk)),SET.NAME("LOptQmk",FALSE))
                                                                                                                                                                                        =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&"=END.IF() > Finished!")              =CLEAR(3)
                 =SET.NAME("Asep",":")                                                                                                                                                  =RETURN()                                              =FOR("Icol",4,ColLst,1)
                                                                                                                                                                                                                  =SET.NAME("LOptQmk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx5row&Acol&2,FALSE)))
                                                                                                                                                                                                                                                                           =FORMULA("Status of Report Sheets",INDIRECT("
                                                                                                                                                                                                                                               = SET.NAME("Atmp",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Icol&A
                 ! Set some useful globals                                                                                                                                                                                                     = IF(Atmp<0.5,SET.NAME("ColLst",Icol-1))
                                                                                                                                                                                                                                                                            > Finished!")
                                                                                                                                                                                                                  =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&"=SELECT(INDIRECT("'"&S0con&"'!"&Arow&1&Acol
                 =SET.NAME("Achr",CHAR(10))                                                                                                                                                                       =RETURN()                    =NEXT()                     =ALIGNMENT(7,FALSE,3,0)
                 =SET.NAME("Nlne0",3)                                                                                                                                                                                                          =IF(ColLst<3.5)             =FONT.PROPERTIES("Arial","Bold",10,,,,,,,1)
                 =SET.NAME("Nblock",4)                                                                                                                                                                                                                                     =FORMULA("Status",INDIRECT("'"&S0con&"'!"&Aro
                                                                                                                                                                                                                                               = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... must
                                                                                                                                                                                                                                                                           =FORMULA("Last
                                                                                                                                                                                                                                               = FORMULA.GOTO(Arow&1&Acol&1,TRUE) Called",INDIRECT("'"&S0con&"'!"
                 ! Store a few workspace parameters                                                                                                                                                                                            = ECHO(TRUE)                =FORMULA("Sheet",INDIRECT("'"&S0con&"'!"&Aro
                 =FORMULA(GET.WORKSPACE(4),INDIRECT("'"&S0mac&"'!"&Arow&2&Acol&3,FALSE))                                                                                                                                                                                   =FORMULA("Contains",INDIRECT("'"&S0con&"'!"&A
                                                                                                                                                                                                                                               = ALERT("At least loci must be specified."&Achr&Achr&"Please correct and try a
                 =FORMULA(GET.DOCUMENT(14),INDIRECT("'"&S0mac&"'!"&Arow&3&Acol&3,FALSE))                                                                                                                                                       = HALT()                    =SELECT(INDIRECT("'"&S0con&"'!"&Arow&2&Acol
                 =FORMULA(NOW(),INDIRECT("'"&S0mac&"'!"&Arow&4&Acol&3,FALSE))                                                                                                                                                                  =END.IF()                   =ALIGNMENT(3,FALSE,3,0)
                                                                                                                                                                                                                                                                           =BORDER(1)
                 ! Turn calculation off and set to R1C1                                                                                                                                                                                        ! Sort into standard order
                 =OPTIONS.CALCULATION(1)                                                                                                                                                                                                                                   =FORMULA("Counts",INDIRECT("'"&S0con&"'!"&Ar
                                                                                                                                                                                                                                               =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&5&Acol&1&Asep&Arow&RowLst&Aco
                 =A1.R1C1(FALSE)                                                                                                                                                                                                                                           =SELECT(INDIRECT("'"&S0con&"'!"&Arow&1&Acol
                                                                                                                                                                                                                                               =SORT(1,Arow&5&Acol&2,1,,,,,2)
                 =COLOR.PALETTE("")                                                                                                                                                                                                                                        =ALIGNMENT(7,FALSE,3,0)
                                                                                                                                                                                                                                               =SET.NAME("RowLst",COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE))+3)
                                                                                                                                                                                                                                                                           =FONT.PROPERTIES("Arial","Bold",10,,,,,,,1)
                 ! Is this the first time for a new user?                                                                                                                                                                                                                  =FORMULA("Type",INDIRECT("'"&S0con&"'!"&Arow
                                                                                                                                                                                                                                               ! Check row/column recognition
                 =SET.NAME("Atmp",DEREF(INDIRECT("'"&S0mac&"'!"&Arow&6&Acol&3,FALSE)))                                                                                                                                                                                     =FORMULA("#",INDIRECT("'"&S0con&"'!"&Arow&2
                                                                                                                                                                                                                                               =SET.NAME("Atitle",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&1,FALSE)
                 =SET.NAME("Atmp1",GET.WORKSPACE(26))                                                                                                                                                                                          =IF(LEN(Atitle)<4.5)        =SELECT(INDIRECT("'"&S0con&"'!"&Arow&2&Acol
                 =FORMULA(Atmp1,INDIRECT("'"&S0mac&"'!"&Arow&6&Acol&3,FALSE))                                                                                                                                                                                              =ALIGNMENT(3,FALSE,3,0)
                                                                                                                                                                                                                                               = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the tit
                 =IF(Atmp=Atmp1)                                                                                                                                                                                                                                           =BORDER(1)
                                                                                                                                                                                                                                               = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                 = SET.NAME("Atmp2",DEREF(INDIRECT("'"&S0mac&"'!"&Arow&7&Acol&3,FALSE)))                                                                                                                                                       = ECHO(TRUE)                =FORMULA("Total",INDIRECT("'"&S0con&"'!"&Arow
                 = IF(ISERROR(Atmp2),SET.NAME("Atmp2",0))                                                                                                                                                                                                                  =FORMULA("Exact Match",INDIRECT("'"&S0con&"
                                                                                                                                                                                                                                               = ALERT("The first column of the first row must hold a dataset Title of at least fiv
                 = SET.NAME("Atmp2",Atmp2+1)                                                                                                                                                                                                   = HALT()                    =FORMULA("Unmatched",INDIRECT("'"&S0con&"'!
                 =ELSE()                                                                                                                                                                                                                       =END.IF()                   =FORMULA("Unmatch (able?)",INDIRECT("'"&S0co
                 = SET.NAME("Atmp2",1)                                                                                                                                                                                                                                     =FORMULA("Off-ladder alleles",INDIRECT("'"&S0co
                                                                                                                                                                                                                                               =IF(COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&2,FALSE))>0)
                 =END.IF()                                                                                                                                                                                                                                                 =FORMULA("Deleted blanks",INDIRECT("'"&S0con
                                                                                                                                                                                                                                               = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the se
                 =FORMULA(Atmp2,INDIRECT("'"&S0mac&"'!"&Arow&7&Acol&3,FALSE))                                                                                                                                                                                              =FORMULA("Deleted 'OR's",INDIRECT("'"&S0con&
                                                                                                                                                                                                                                               = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&2,FALSE))
                                                                                                                                                                                                                                               = ECHO(TRUE)                =FORMULA("Deleted '?'s",INDIRECT("'"&S0con&"'!
                 ! ControlPanel locations                                                                                                                                                                                                      = ALERT("The second row is expected to be blank."&Achr&Achr&"Please correc
                 =SET.NAME("Ched1col",4)                                                                                                                                                                                                       = HALT()                    =FORMULA.ARRAY("Clear",INDIRECT("'"&S0con&
                 =SET.NAME("Ched2col",Ched1col+5)                                                                                                                                                                                              =END.IF()                   =FORMULA.ARRAY(NOW(),INDIRECT("'"&S0con&
                 =SET.NAME("Ched3col",Ched2col+3)                                                                                                                                                                                                                          =FORMULA(MID(S0dat,FIND("]",S0dat)+1,100),IND
                                                                                                                                                                                                                                               =IF(COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&3,FALSE))>0)
                 =SET.NAME("Cbut0row",1)                                                                                                                                                                                                                                   =FORMULA("Comma-delimited STR-profiles the se
                                                                                                                                                                                                                                               = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error...for all
                 =SET.NAME("Cbut1row",6)                                                                                                                                                                                                                                   =FOR("Itmp",1,Nrepsht,1)
                                                                                                                                                                                                                                               = SELECT(INDIRECT("'"&S0dat&"'!"&Acol&3,FALSE))
                 =SET.NAME("Cbut2row",11)                                                                                                                                                                                                      = ECHO(TRUE)                = SET.NAME("S0rep",EVALUATE("S0rp"&Itmp))



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                             11/18/2010 6:23 PM                                                                                                                                           8 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                 Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                 =SET.NAME("Cbut3row",16)                                                                                                                    = FORMULA(MID(S0rep,FIND("]",S0rep)+1,100),IN
                                                                                                                                  = ALERT("The third column is expected to be blank."&Achr&Achr&"Please correc
                 =SET.NAME("Cbut4row",21)                                                                                         = HALT()                   = ACTIVATE(S0rep)
                 =SET.NAME("Cbut5row",26)                                                                                         =END.IF()                  = SELECT(INDIRECT("'"&S0rep&"'!"&Acol&1&Asep
                 =SET.NAME("Cbut6row",29)                                                                                                                    = EDIT.DELETE()
                                                                                                                                  =IF(DEREF(INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&1,FALSE))<>"SampleCode
                 =SET.NAME("Cbut7row",32)                                                                                                                    = COLUMN.WIDTH(0.5)
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the fir
                 =SET.NAME("Cbut8row",36)                                                                                                                    = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                  = FORMULA.GOTO(Arow&3&Acol&1,FALSE)
                                                                                                                                  = ECHO(TRUE)               =NEXT()
                 =SET.NAME("Ckbx1row",25)                                                                                         = ALERT("The first column of the third row is expected to be 'SampleCode'."&Ac
                 =SET.NAME("Ckbx2row",26)                                                                                         = HALT()                   ! Update the RepDetail cutoffs
                 =SET.NAME("Ckbx3row",16)                                                                                         =END.IF()                  =RUN(SMS_CheckCutoffs,FALSE)
                 =SET.NAME("Ckbx4row",17)                                                                                         =IF(DEREF(INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&4,FALSE))<>"Loci")
                 =SET.NAME("Ckbx5row",18)                                                                                                                    ! Update the data counts
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the fo
                                                                                                                                                             =RUN(SMS_DataUpdate,FALSE)
                                                                                                                                  = FORMULA.GOTO(Arow&3&Acol&4,FALSE)
                 =RETURN()                                                                                                        = ECHO(TRUE)
                                                                                                                                                              of the third row
                                                                                                                                  = ALERT("The fourth column=ECHO(TRUE) is expected to be 'Loci'."&Achr&Ach
                                                                                                                                  = HALT()                   =FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALS
                                                                                                                                  =END.IF()
                                                                                                                                                             =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&
                                                                                                                                  =IF(DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&1,FALSE))<>"Type")
                                                                                                                                                             =RETURN()
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the fir
                                                                                                                                  = FORMULA.GOTO(Arow&4&Acol&1,FALSE)
                                                                                                                                  = ECHO(TRUE)
                                                                                                                                  = ALERT("The first column of the fourth row is expected to be 'Type'."&Achr&Ach
                                                                                                                                  = HALT()
                                                                                                                                  =END.IF()
                                                                                                                                  =IF(DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&2,FALSE))<>"Description")
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... the se
                                                                                                                                  = FORMULA.GOTO(Arow&4&Acol&2,FALSE)
                                                                                                                                  = ECHO(TRUE)
                                                                                                                                  = ALERT("The second column of the fourth row is expected to be 'Description'."&
                                                                                                                                  = HALT()
                                                                                                                                  =END.IF()

                                                                                                                                  ! Check the locus names
                                                                                                                                  =FOR("Icol",4,ColLst,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Icol,FAL
                                                                                                                                  = IF(LEN(Atmp)<2.5)
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... Locu
                                                                                                                                  = FORMULA.GOTO(Arow&4&Acol&Icol,FALSE)
                                                                                                                                  = ECHO(TRUE)
                                                                                                                                  = ALERT("Locus names ("&Atmp&") are expected to have at least 3 characters
                                                                                                                                  = HALT()
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Check that there's actually some data at each locus
                                                                                                                                  =FOR("Icol",4,ColLst,1)
                                                                                                                                  = SET.NAME("Atmp",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&5&Acol&Icol&A
                                                                                                                                  = IF(Atmp<0.5)
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Data error... At le
                                                                                                                                  = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Icol&Asep&Arow&65536&
                                                                                                                                  = ECHO(TRUE)
                                                                                                                                  = ALERT("There are no alleles specified in any sample at this locus."&Achr&Ac
                                                                                                                                  = HALT()
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Dump any crap
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&ColLst+1&Asep&Acol&256,FALSE))
                                                                                                                                  =EDIT.DELETE(4)
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&RowLst+1&Asep&Arow&65536,FALS
                                                                                                                                  =EDIT.DELETE(4)
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                                                                  =COLUMN.WIDTH(0.5)
                                                                                                                                  =COLUMN.WIDTH(,,,3)
                                                                                                                                  =FORMULA.GOTO(Arow&1&Acol&1,TRUE)

                                                                                                                                  ! Validate the alleles
                                                                                                                                  =SET.NAME("Imsg",0)
                                                                                                                                  =SET.NAME("Nloc",ColLst-3)
                                                                                                                                  =SET.NAME("Nmsg",(RowLst-4)*Nloc)
                                                                                                                                  =SET.NAME("Nofl",0)
                                                                                                                                  =SET.NAME("Nblk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx3row&Acol&C
                                                                                                                                  =IF(ISERROR(Nblk*1),SET.NAME("Nblk",0))



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                    9 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                            Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                                                  =SET.NAME("Naor",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx4row&Acol&C
                                                                                                                                  =IF(ISERROR(Naor*1),SET.NAME("Naor",0))
                                                                                                                                  =SET.NAME("Nqmk",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Ckbx5row&Acol&
                                                                                                                                  =IF(ISERROR(Nqmk*1),SET.NAME("Nqmk",0))

                                                                                                                                  ! Loop over all samples
                                                                                                                                  =ACTIVATE(S0dat)
                                                                                                                                  =FOR("Irow",5,RowLst,1)

                                                                                                                                  ! Loop over all samples
                                                                                                                                  = SET.NAME("NvalLoc",0)
                                                                                                                                  = FOR("Icol",4,Nloc+3,1)
                                                                                                                                  = SET.NAME("Imsg",Imsg+1)
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Checking alleles…
                                                                                                                                  = ACTIVATE(S0dat)
                                                                                                                                  = SET.NAME("Atmp",CLEAN(TRIM(UPPER(INDIRECT("'"&S0dat&"'!"&Arow&Ir
                                                                                                                                  = IF(ISNA(Atmp),SET.NAME("Atmp",-999))
                                                                                                                                  = IF(OR(Atmp="?",Atmp=0,Atmp="0",Atmp="'0",Atmp="INC",Atmp="NA",Atmp=
                                                                                                                                  = IF(LEN(Atmp)<0.5,SET.NAME("Atmp",-999))

                                                                                                                                  ! Is it a null?
                                                                                                                                  = IF(Atmp=-999)
                                                                                                                                  =      SET.NAME("Anew","?")

                                                                                                                                  ! Check for " OR "
                                                                                                                                  = ELSE.IF(NOT(ISERROR(FIND(" OR ",Atmp))))
                                                                                                                                  =    IF(LOptAor)
                                                                                                                                  =     SET.NAME("Naor",Naor+1)
                                                                                                                                  =     SET.NAME("Anew","?")
                                                                                                                                  =    ELSE()
                                                                                                                                  =      MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Ambiguous 'O
                                                                                                                                  =      FORMULA.GOTO(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALS
                                                                                                                                  =      SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALSE))
                                                                                                                                  =      PATTERNS(1,6,0)




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                              10 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                 Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                                                  =     ECHO(TRUE)
                                                                                                                                  =     ALERT("This 'OR' profile ("&Atmp&") is ambiguous."&Achr&Achr&"Please
                                                                                                                                  =     HALT()
                                                                                                                                  =    END.IF()

                                                                                                                                  ! Not a null - standardize it
                                                                                                                                  = ELSE()
                                                                                                                                  =    SET.NAME("Atmp",SUBSTITUTE(Atmp," ",""))
                                                                                                                                  =    SET.NAME("Atmp",SUBSTITUTE(Atmp,"'",""))
                                                                                                                                  =    SET.NAME("Atmp",SUBSTITUTE(Atmp,Alsp&Alsp,Alsp))
                                                                                                                                  =     WHILE(NOT(ISERROR(FIND(Alsp&Alsp,Atmp))))
                                                                                                                                  =      SET.NAME("Atmp",SUBSTITUTE(Atmp,Alsp&Alsp,Alsp))
                                                                                                                                  =    NEXT()
                                                                                                                                  =    SET.NAME("Atmp",SUBSTITUTE(Atmp,Alsp&"0",Alsp&"?"))
                                                                                                                                  =    IF(LEFT(Atmp,1)="0",SET.NAME("Atmp","?"&MID(Atmp,2,100)))

                                                                                                                                  ! Check that it doesn't have strange characters in it
                                                                                                                                  =    SET.NAME("Nchr",LEN(Atmp))
                                                                                                                                  =    FOR("Ichr",1,Nchr,1)
                                                                                                                                  =     SET.NAME("Atmp1",MID(Atmp,Ichr,1))
                                                                                                                                  =     SET.NAME("Itmp",MATCH(Atmp1,INDIRECT("'"&S0wrk&"'!"&Acol&1,FALS
                                                                                                                                  =     IF(ISERROR(Itmp))
                                                                                                                                  =      MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Unexpected c
                                                                                                                                  =      FORMULA.GOTO(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALS
                                                                                                                                  =      SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALSE))
                                                                                                                                  =      PATTERNS(1,6,0)
                                                                                                                                  =      ECHO(TRUE)
                                                                                                                                  =      ALERT("This profile contains the unexpected character: "&Atmp1&"."&Ac
                                                                                                                                  =      HALT()
                                                                                                                                  =     END.IF()
                                                                                                                                  =    NEXT()

                                                                                                                                  ! Make sure alleles are unique and in sort order
                                                                                                                                  =    SELECT(INDIRECT("'"&S0dat&"'!"&Acol&256,FALSE))
                                                                                                                                  =    CLEAR(3)
                                                                                                                                  =    SET.NAME("Iall",0)
                                                                                                                                  =    SET.NAME("Istr",1)
                                                                                                                                  =    SET.NAME("Anew",Atmp&Alsp)
                                                                                                                                  =    SET.NAME("Ntmp",LEN(Atmp))
                                                                                                                                  =    FOR("Itmp",1,Ntmp,1)
                                                                                                                                  =     SET.NAME("Itmp",FIND(Alsp,Anew,Istr))
                                                                                                                                  =     SET.NAME("Atmp1",MID(Anew,Istr,Itmp-Istr))
                                                                                                                                  =     IF(NOT(ISERROR(Atmp1+0)),SET.NAME("Atmp1",Atmp1+0))
                                                                                                                                  =     SET.NAME("Istr",Itmp+1)
                                                                                                                                  =     SET.NAME("Iall",Iall+1)
                                                                                                                                  =     FORMULA(Atmp1,INDIRECT("'"&S0dat&"'!"&Arow&Iall&Acol&256,FALSE)
                                                                                                                                  =     IF(Atmp1="OL")
                                                                                                                                  =      SET.NAME("Nofl",Nofl+1)
                                                                                                                                  =      SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALSE))
                                                                                                                                  =      PATTERNS(1,4,0)
                                                                                                                                  =      END.IF()
                                                                                                                                  =    NEXT()
                                                                                                                                  =    SELECT(INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&256&Asep&Arow&Iall&A
                                                                                                                                  =    SORT(1)
                                                                                                                                  =    SET.NAME("Anew",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&256,F
                                                                                                                                  =    FOR("Itmp",2,Iall,1)
                                                                                                                                  =     IF(DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Itmp-1&Acol&256,FALSE))<>D
                                                                                                                                  =    NEXT()

                                                                                                                                  ! Has it changed?
                                                                                                                                  =    IF(Atmp=Anew)
                                                                                                                                  =      IF(Anew=0,SET.NAME("Anew","?"))

                                                                                                                                  ! If the lengths are the same, ok… just replace
                                                                                                                                  =      ELSE.IF(LEN(Atmp)=LEN(Anew))
                                                                                                                                  =       IF(Anew=0,SET.NAME("Anew","?"))

                                                                                                                                  ! If is just an expanded form, ok…
                                                                                                                                  =      ELSE.IF(Atmp=Anew&Alsp&Anew)
                                                                                                                                  =        IF(Anew=0,SET.NAME("Anew","?"))

                                                                                                                                  ! Any other difference
                                                                                                                                  =    ELSE()



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                   11 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                               Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                                                  =     MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Unexpected ch
                                                                                                                                  =     SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALSE))
                                                                                                                                  =     PATTERNS(1,5,0)
                                                                                                                                  =     ECHO(TRUE)
                                                                                                                                  =     SET.NAME("Atmp1",ALERT("Are the specified profile ("&Atmp&") and the
                                                                                                                                  =     IF(NOT(Atmp1))
                                                                                                                                  =      MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Returning to
                                                                                                                                  =      ALERT("Please correct the hassle and try again.",1)
                                                                                                                                  =      HALT()
                                                                                                                                  =     END.IF()
                                                                                                                                  =    END.IF()
                                                                                                                                  =   END.IF()

                                                                                                                                  ! Count the unique alleles
                                                                                                                                  = IF(NOT(Anew="?"),SET.NAME("NvalLoc",NvalLoc+1))

                                                                                                                                  ! Check for ",?" and "?," and "?"
                                                                                                                                  = IF(LOptQmk)
                                                                                                                                  =    IF(NOT(ISERROR(FIND(",?",Anew))))
                                                                                                                                  =     SET.NAME("Nqmk",Nqmk+1)
                                                                                                                                  =     SET.NAME("Anew",SUBSTITUTE(Anew,",?",""))
                                                                                                                                  =    END.IF()
                                                                                                                                  =    IF(NOT(ISERROR(FIND("?,",Anew))))
                                                                                                                                  =     SET.NAME("Nqmk",Nqmk+1)
                                                                                                                                  =     SET.NAME("Anew",SUBSTITUTE(Anew,"?,",""))
                                                                                                                                  =    END.IF()
                                                                                                                                  =    IF(Anew="?")
                                                                                                                                  =     SET.NAME("Nqmk",Nqmk+1)
                                                                                                                                  =     SET.NAME("Anew","")
                                                                                                                                  =    END.IF()
                                                                                                                                  = END.IF()

                                                                                                                                  ! Store it!
                                                                                                                                  = FORMULA(Anew,INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Icol,FALSE))
                                                                                                                                  = NEXT()

                                                                                                                                  ! Is this a blank record?
                                                                                                                                  = IF(AND(NvalLoc<0.5,LOptBlk))
                                                                                                                                  =     SET.NAME("Nblk",Nblk+1)
                                                                                                                                  =     SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow,FALSE))
                                                                                                                                  =     CLEAR(3)
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Make the hash code for every non-blank sample
                                                                                                                                  =FOR("Irow",5,RowLst,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&4,FAL
                                                                                                                                  = FOR("Iloc",2,Nloc,1)
                                                                                                                                  = SET.NAME("Atmp",Atmp&";"&INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Ilo
                                                                                                                                  = NEXT()
                                                                                                                                  = FORMULA(Atmp,INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Nloc+5,FALSE)
                                                                                                                                  =NEXT()

                                                                                                                                  ! Sort by hash and sample name
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&5&Acol&1&Asep&Arow&RowLst&Aco
                                                                                                                                  =SORT(1,Arow&5&Acol&Nloc+5,1,Arow&5&Acol&2,1,,,2)

                                                                                                                                  ! Combine matching samples
                                                                                                                                  =FOR("Irow",5,RowLst-1,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Nloc+
                                                                                                                                  = SET.NAME("Itmp",MATCH(Atmp,INDIRECT("'"&S0dat&"'!"&Acol&Nloc+5,FAL

                                                                                                                                  ! If hash matches, eliminate things with same label
                                                                                                                                  = IF(Itmp>Irow)
                                                                                                                                  = SET.NAME("Alab",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2,FA
                                                                                                                                  = SET.NAME("Alab1",Alab)
                                                                                                                                  = FOR("Jrow",Irow+1,Itmp,1)
                                                                                                                                  =     SET.NAME("Alab2",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&2,
                                                                                                                                  =     SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&1&Asep&Arow&Jrow&
                                                                                                                                  =     CLEAR(3)
                                                                                                                                  =     PATTERNS(0)
                                                                                                                                  =     IF(Alab1<>Alab2)
                                                                                                                                  =      SET.NAME("Alab",Alab&", "&Alab2)



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                 12 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                 Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                                                  =    SET.NAME("Alab1",Alab2)
                                                                                                                                  =   END.IF()
                                                                                                                                  = NEXT()
                                                                                                                                  = IF(Alab<>Alab1)
                                                                                                                                  =   FORMULA("ExactMatch",INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&1,FAL
                                                                                                                                  =   FORMULA(Alab,INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2,FALSE))
                                                                                                                                  = END.IF()
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Clear the workarea and resort
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&Nloc+4&Asep&Acol&256,FALSE))
                                                                                                                                  =CLEAR(3)
                                                                                                                                  =PATTERNS(0)
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&5&Acol&1&Asep&Arow&RowLst&Aco
                                                                                                                                  =SORT(1,Arow&5&Acol&1,1,Arow&5&Acol&2,1,,,2)

                                                                                                                                  ! Find any non-match replicate long-names
                                                                                                                                  =SET.NAME("Nexa",COUNTIF(INDIRECT("'"&S0dat&"'!"&Acol&1,FALSE),"=Exa
                                                                                                                                  =SET.NAME("RowLst",COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE))+3)
                                                                                                                                  =FOR("Irow",Nexa+5,RowLst,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2,FAL
                                                                                                                                  = SET.NAME("Itmp",Nexa+4+MATCH(Atmp,INDIRECT("'"&S0dat&"'!"&Arow&Ne

                                                                                                                                  ! If necessary, make the long-names unique
                                                                                                                                  = IF(Itmp>Irow)
                                                                                                                                  = FOR("Jrow",Irow,Itmp,1)
                                                                                                                                  =     SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&2,F
                                                                                                                                  =     FORMULA(Atmp&"|"&Jrow-Irow+1,INDIRECT("'"&S0dat&"'!"&Arow&Jrow&A
                                                                                                                                  = NEXT()
                                                                                                                                  = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2&Asep&Arow&Itmp&A
                                                                                                                                  = PATTERNS(1,8)
                                                                                                                                  = SET.NAME("Irow",Itmp)
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Make the short names… the stuff to the left of the first separater ( :-_|/\)
                                                                                                                                  =FOR("Irow",Nexa+5,RowLst,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2,FAL
                                                                                                                                  = SET.NAME("Nchr",LEN(Atmp))
                                                                                                                                  = FOR("Ichr",2,Nchr,1)
                                                                                                                                  = SET.NAME("Atmp1",MID(Atmp,Ichr,1))
                                                                                                                                  = IF(OR(Atmp1=" ",Atmp1=":",Atmp1="-",Atmp1="_",Atmp1="|",Atmp1=".",Atmp
                                                                                                                                  =    SET.NAME("Atmp",LEFT(Atmp,Ichr-1))
                                                                                                                                  =    SET.NAME("Ichr",Nchr+1)
                                                                                                                                  = END.IF()
                                                                                                                                  = FORMULA(Atmp,INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&5+Nloc,FALSE
                                                                                                                                  = NEXT()
                                                                                                                                  =NEXT()

                                                                                                                                  ! Try to match short name
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&5+Nexa&Acol&1&Asep&Arow&RowL
                                                                                                                                  =SORT(1,Arow&Nexa+6&Acol&Nloc+5,1,Arow&Nexa+6&Acol&1,1,,,2)
                                                                                                                                  =FOR("Irow",Nexa+5,RowLst,1)
                                                                                                                                  = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&5+Nlo
                                                                                                                                  = SET.NAME("Itmp",Nexa+4+MATCH(Atmp,INDIRECT("'"&S0dat&"'!"&Arow&Ne
                                                                                                                                  = IF(Itmp>Irow)
                                                                                                                                  = FORMULA.ARRAY("UnMatched",INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol
                                                                                                                                  = SET.NAME("Irow",Itmp)
                                                                                                                                  = ELSE()
                                                                                                                                  = FORMULA("UnMatched (able?)",INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&
                                                                                                                                  = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&1,FALSE))
                                                                                                                                  = PATTERNS(1,7)
                                                                                                                                  = END.IF()
                                                                                                                                  =NEXT()
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&4+Nloc&Asep&Acol&256,FALSE))
                                                                                                                                  =CLEAR(3)

                                                                                                                                  ! Leave it sorted by Type and long name
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Arow&5&Acol&1&Asep&Arow&RowLst&Aco
                                                                                                                                  =SORT(1,Arow&5&Acol&1,2,Arow&5&Acol&2,1,,,2)
                                                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                                                                  =COLUMN.WIDTH(0.5)



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                   13 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                   Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                                                  =COLUMN.WIDTH(,,,3)
                                                                                                                                  =FORMULA.GOTO(Arow&1&Acol&1,TRUE)

                                                                                                                                  ! Update the data counts
                                                                                                                                  =FORMULA("Checked",INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col,FAL
                                                                                                                                  =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col+1,FALS
                                                                                                                                  =RUN(SMS_DataUpdate,FALSE)


                                                                                                                                  ! Off-ladder alleles
                                                                                                                                  =ACTIVATE(S0con)
                                                                                                                                  =FORMULA(Nofl,INDIRECT("'"&S0con&"'!"&Arow&8&Acol&Ched2col+1,FALSE)
                                                                                                                                  =IF(Nofl>0.5)
                                                                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > There are "&Nofl&
                                                                                                                                  = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                  = FORMULA.GOTO(Arow&8&Acol&Ched2col,FALSE)
                                                                                                                                  = ECHO(TRUE)
                                                                                                                                  = ALERT("There "&IF(Nofl<1.5,"is","are up to ")&" "&Nofl&" 'OL' (off-ladder) allele
                                                                                                                                  =END.IF()

                                                                                                                                  ! Option alerts
                                                                                                                                  =FORMULA(Nblk,INDIRECT("'"&S0con&"'!"&Arow&Ckbx3row&Acol&Ched2col+1
                                                                                                                                  =FORMULA(Naor,INDIRECT("'"&S0con&"'!"&Arow&Ckbx4row&Acol&Ched2col+1
                                                                                                                                  =FORMULA(Nqmk,INDIRECT("'"&S0con&"'!"&Arow&Ckbx5row&Acol&Ched2col+

                                                                                                                                  =ECHO(TRUE)
                                                                                                                                  =FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)

                                                                                                                                  =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                                                  =RETURN()




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                     14 / 38
Built by: DL Duewer, NIST
                                                                                                                   STR_MatchSamples <Version 2-Jun-09>                                                                                                                          Paid for, in part, by: NIJ

                                                                                                     A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                   SMS_Control                 SMS_DataAdd           SMS_DataUpdate          SMS_GetData                 SMS_GetWorksheet            SMS_Print         SMS_Print1       SMS_Print2          SMS_Print3               SMS_ReadMe1
                                               ! Get the
                   ! Take user to the Control worksheetfirst dataset ! Get current Counts    ! Get the names and alleles !for the samples from this worksheet
                                                                                                                           Get the data from one worksheetactive sheet
                                                                                                                                                     ! Print           ! Print Rep1     ! Print Rep1        ! Print Rep1             ! Take a new user to the ReadMe worksheet
                                               =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)=MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)=MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R
                   =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)=MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                   =ECHO(FALSE)                =ECHO(FALSE)          =ECHO(FALSE)            =ECHO(FALSE)                =ECHO(TRUE)                 =ECHO(FALSE)      =ECHO(FALSE)     =ECHO(FALSE)        =ECHO(FALSE)             =ECHO(FALSE)

                   =ACTIVATE(S0con)     ! Are data already loaded? =ACTIVATE(S0con)               ! Loop over the samples ! Find the new file               ! Check if it is ready       =SET.NAME("Irow",4)      =SET.NAME("Irow",5)          =SET.NAME("Irow",6)  ! Make sure Intro is in right format
                                        =SET.NAME("NsamOld",COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&1,FALSE))-3)
                   =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                              =SET.NAME("Asht",MID(S1dat,FIND("]",S1dat)+1,100)) =SET.NAME("Atmp",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Irow&Acol&4,FALSE)))=SET.NAME("Igoto",Cbut7row)
                                                                                                                               =SET.NAME("Nloc",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&4,FALSE))-2)
                                                                     =SET.NAME("Itmp",COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE))-1)                                                                            =SET.NAME("Igoto",Cbut6row)
                                                                                                                                                                                         =SET.NAME("Igoto",Cbut5row)                                                =ACTIVATE(S0int)
                                        =IF(NsamOld<1.5)
                   =FORMULA.GOTO(Arow&6&Acol&1,FALSE)                                             =SET.NAME("Nsam",COUNTA(INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE))-1)
                                                                                                                               =SET.NAME("Afile",OPEN.DIALOG(,,"Please select the desired file."))
                                                                     =FORMULA(MAX(0,Itmp),INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched2col+1,FALSE))            =IF(Atmp<>"Finished")        =SET.NAME("S0rep",S0rp1)=SET.NAME("S0rep",S0rp2)=SET.NAME("S0rep",S0rp3)=FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                  =ACTIVATE(S0dat)             =IF(ISERROR(Afile),SET.NAME("Afile",FALSE))
                                                                                                                                                            =
                                                                     =SET.NAME("Itmp",COUNTIF(INDIRECT("'"&S0dat&"'!"&Acol&1,FALSE),"=ExactMatch")) SET.NAME("Atmp1",MID(S0rep,FIND("]",S0rep)+1,100))                                                              =FORMULA.GOTO(Arow&27&Acol&4,FALSE)
                                        ! Clear everything            > Finished!")               =FOR("Irow",RowHed+1,RowLst,1)
                                                                                                                               =IF(Afile=FALSE)
                   =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&"=FORMULA(Itmp,INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched2col+1,FALSE))                       = ACTIVATE(S0con)            =RUN(SMS_Print,FALSE) =RUN(SMS_Print,FALSE) =RUN(SMS_Print,FALSE)
                   =RETURN()                                                                                                   = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Browsing aborted "&R1C&" > Error… The report sheet ("&Atmp1&") has not been !Finished!")
                                        = RUN(SMS_Clear,FALSE) =SET.NAME("Jtmp",COUNTIF(INDIRECT("'"&S0dat&"'!"&Acol&1,FALSE),"=Unmatched=(able?)"))           MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" by user.")
                                                                                                  = SET.NAME("Atmp2",COUNTA(INDIRECT("'"&S1dat&"'!"&Arow&Irow,FALSE)))                                                                                                Go to ReadMe
                                                                     =FORMULA(Jtmp,INDIRECT("'"&S0con&"'!"&Arow&6&Acol&Ched2col+1,FALSE))
                                        = FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALSE)                = IF(Atmp2>0.5)              = ACTIVATE(S0con)            = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                                                                         =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                                                                                                                                                               =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                                                                                                                                                                                    =ACTIVATE(S0rme)
                                                                                                                                                                                                                  =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                  = SET.NAME("Nsam",Nsam+1)    = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                                            =                            =RETURN()
                                                                     =SET.NAME("Itmp",COUNTIF(INDIRECT("'"&S0dat&"'!"&Acol&1,FALSE),"=Unmatched")) FORMULA.GOTO(Arow&Cbut1row&Acol&1,FALSE) =RETURN()                                          =RETURN()            =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                        ! Get the incoming data                                   = IF(MOD(Nsam,2)=1,MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Processing "&Irow-RowHed&" / "&RowLst-RowHed))
                                                                                                                               = FORMULA.GOTO(Arow&IF(Nloc<1.5,Cbut2row,Cbut3row)&Acol&1,FALSE)
                                                                     =FORMULA(Itmp+Jtmp,INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched2col+1,FALSE))= ECHO(TRUE)                                                                                                          =ECHO(TRUE)
                                        = RUN(SMS_GetWorksheet,FALSE)                                                                                       = ALERT("The report ("&Atmp1&") has not you generated."&Achr&Achr&"Please define the data and matchs and=ALERT("This worksheet is the 'Instruction Manual' fo
                                                                                                                               = ALERT("No data file was specified."&Achr&Achr&"Please do whatever beenneed to do and try again.",2)                                 try again.",2)
                                                                                                  ! Name                       = HALT()                     = HALT()
                                        ! Format the Controlpanel's worksheet list                                             =END.IF()                    =END.IF()
                                                                                                  = FORMULA("Unmatched",INDIRECT("'"&S0dat&"'!"&Arow&4+Nsam&Acol&1,FALSE))                                                                                          =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R
                                        = ECHO(FALSE)                                             = SET.NAME("Atmp",DEREF(INDIRECT("'"&S1dat&"'!"&Arow&Irow&Acol&ColHed,FALSE)))
                                                                     ! Match to all or only to unmatched?                                                                                                                                                           =RETURN()
                                        = ACTIVATE(S0con)                                         = FORMULA(Atmp&":"&Asht,INDIRECT("'"&S0dat&"'!"&Arow&4+Nsam&Acol&2,FALSE))
                                                                                                                               ! Open the file
                                                                     =SET.NAME("IsAll",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Cbut4row&Acol&1,FALSE)))          ! Offer to print the specified sheet
                                                                     =IF(IsAll<>2,SET.NAME("IsAll",1))
                                        = SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched3col+1,FALSE))                               =ERROR(FALSE)                =ACTIVATE(S0rep)
                                        = FORMAT.NUMBER(AlngDatTim)                               ! Alleles
                                                                     =SET.NAME("Atmp",IF(IsAll=1,Amat1_txt,Amat2_txt))                                      =ECHO(TRUE)
                                                                                                                               = SET.NAME("Atmp1",OPEN(Afile,0,FALSE,,,,,,,,1))
                                                                     =
                                        = ALIGNMENT(4,FALSE,3,0) FOR("Itmp",1,Nrepsht,1)          = FOR("Iloc",1,Nloc,1)       =ERROR(TRUE)                 =PRINT?(1,,,1,,,,,1,,,2)
                                                                     = SET.NAME("Arep",EVALUATE("S0rp"&Itmp&"_txt"))
                                                                                                  =     SET.NAME("Jcol",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE)))
                                        = FORMULA("Worksheets Loaded",INDIRECT("'"&S0con&"'!"&Arow&1&Acol&Ched3col,FALSE))     =IF(Atmp1=FALSE)             =ECHO(FALSE)
                                                                     = FORMULA(Arep&Atmp,INDIRECT("'"&S0con&"'!"&Arow&3+Itmp&Acol&Ched1col+3,FALSE))
                                                                                                  =     IF(Jcol>0.5)           = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Workbook ("&Afile&") could not be opened.")
                                        = SELECT(INDIRECT("'"&S0con&"'!"&Arow&1&Acol&Ched3col&Asep&Arow&1&Acol&Ched3col+3,FALSE))
                                                                     =
                                        = ALIGNMENT(7,FALSE,3,0) NEXT()                                                        = ACTIVATE(S0con)            ! Go back to the control panel
                                        = FONT.PROPERTIES("Arial","Bold",10,,,,,,,1)                                                                        =ACTIVATE(S0con)
                                                                                                  ! Only transfer desired loci = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                     ! Format                     =      SET.NAME("Nall",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&4,FALSE)))
                                        = FORMULA("Rows",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col,FALSE))                                               =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                               = FORMULA.GOTO(Arow&IF(Nloc<1.5,Cbut2row,Cbut3row)&Acol&1,FALSE)
                                                                     =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&256,FALSE)) error... Workbook ("&Afile&") could not be opened."&Achr&Achr&"Please contact david.duewer@nist.gov about this hassle.",2)
                                                                                                  =      SET.NAME("Icol",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&5,FALSE)))
                                        = FORMULA("Date Loaded",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col+1,FALSE))         = ALERT("Unexpected          =FORMULA.GOTO(Arow&Igoto&Acol&1,FALSE)
                                                                     =COLUMN.WIDTH(0.5)
                                        = FORMULA("Worksheet",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col+2,FALSE))           = HALT()                     =ECHO(TRUE)
                                                                     =COLUMN.WIDTH(,,,3)          ! Check whether there's at least one allele specified
                                        = FORMULA("Directory",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col+3,FALSE))           =END.IF()
                                                                                                  =      SET.NAME("Atmp","?")
                                        = FORMULA("#Loci",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col+4,FALSE))                                            =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                     =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" Get file info
                                                                                                  =      SET.NAME("Ntmp",COUNTA(INDIRECT("'"&S1dat&"'!"&Arow&Irow&Acol&Icol&Asep&Arow&Irow&Acol&Icol+Nall-1,FALSE)))
                                        = FORMULA("Loci...",INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col+5,FALSE)) ! > Finished!")                          =RETURN()
                                                                     =RETURN()                    =      FOR("Iall",1,Ntmp,1) =ECHO(FALSE)
                                        = SELECT(INDIRECT("'"&S0con&"'!"&Arow&2&Acol&Ched3col&Asep&Arow&2&Acol&Ched3col+4,FALSE))
                                        = ALIGNMENT(3,FALSE,3,0)                                  =                            =SET.NAME("Dir1",GET.DOCUMENT(2))
                                                                                                           SET.NAME("Aloc",UPPER(CLEAN(TRIM(INDIRECT("'"&S1dat&"'!"&Arow&Irow&Acol&Icol+Iall-1,FALSE)))))
                                        = BORDER(1)                                                                            =SET.NAME("WB1",GET.DOCUMENT(88))
                                                                                                  ! Standardize the sucker =SET.NAME("Asht",GET.WORKBOOK(38))
                                        ! Format the Data worksheet                               =                            =SET.NAME("S1dat","["&WB1&"]"&Asht)
                                                                                                           IF(ISERROR(Aloc),SET.NAME("Aloc","?"))
                                        = ACTIVATE(S0dat)                                         =                            =ACTIVATE(S1dat)
                                                                                                           IF(NOT(ISERROR(Aloc+0)),SET.NAME("Aloc",Aloc+0))
                                                                                                  =        IF(Aloc= 0,SET.NAME("Aloc","?"))
                                        = FORMULA("Data from: "&S1dat,INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&1,FALSE)) =SELECT(INDIRECT("'"&S1dat&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                        = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&1&Asep&Arow&1&Acol&3+Nloc,FALSE))         =COLUMN.WIDTH(0.5)
                                        = FONT.PROPERTIES("Arial","Bold",14,,,,,,,1)                                           =COLUMN.WIDTH(,,,3)
                                                                                                  ! Assemble the condensed profile
                                        = ALIGNMENT(7,FALSE,3,0,FALSE)                            =        IF(Iall<1.5)        =ZOOM(75)
                                                                                                  =
                                        = FORMULA("SampleCode",INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&1,FALSE))                   =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                            SET.NAME("Atmp",Aloc)
                                                                                                  =         SET.NAME("Anew",Aloc)
                                        = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&1&Asep&Arow&3&Acol&2,FALSE))
                                        = ALIGNMENT(7,FALSE,3,0,FALSE)                            =        ELSE.IF(Iall<2.5)   ! Is this the right sheet?
                                                                                                  =
                                        = FORMULA("Loci",INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&4,FALSE))      IF(NOT(Aloc= Anew))=SET.NAME("Atmp1",ALERT("Is this the right sheet?"&S1dat&"."&Achr&Achr&"Click 'OK' if 'Yes', 'Cancel' for 'No'.",1))
                                                                                                  =           SET.NAME("Atmp",Atmp&Alsp&Aloc)
                                        = SELECT(INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&4&Asep&Arow&3&Acol&3+Nloc,FALSE))         =IF(Atmp1=FALSE)
                                        = ALIGNMENT(7,FALSE,3,0,FALSE)                            =                            = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Activate the desired sheet, save the file, and try again!")
                                                                                                              SET.NAME("Anew",Aloc)
                                                                                                  =
                                        = FORMULA("Type",INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&1,FALSE))      END.IF()           = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                  =        ELSE()
                                        = FORMULA("Description",INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&2,FALSE))                  = ECHO(TRUE)
                                                                                                  =         IF(NOT(OR(Aloc= Anew,Aloc= "?"))) activate
                                                                                                                               = ALERT("Please
                                        = SELECT("("&Arow&4&Acol&1&Asep&Arow&4&Acol&2&Alsp&Arow&4&Acol&4&Asep&Arow&4&Acol&3+Nloc&")")the desired worksheet, save the workbook and try again.",2)
                                        = ALIGNMENT(3,FALSE,3,0,FALSE)                            =                            = HALT()
                                                                                                              SET.NAME("Atmp",Atmp&Alsp&Aloc)
                                        = BORDER(1)                                               =                            =END.IF()
                                                                                                              SET.NAME("Anew",Aloc)
                                        = FOR("Iloc",1,Nloc,1)                                    =         END.IF()
                                                                                                  =        END.IF()            !
                                        = SET.NAME("Aloc",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&3,FALSE)))Get the basic file parameters
                                                                                                  =
                                        = FORMULA(Aloc,INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Iloc+3,FALSE))NEXT()                =SET.NAME("ColHed",-1)
                                                                                                  =      FORMULA(Atmp,INDIRECT("'"&S0dat&"'!"&Arow&4+Nsam&Acol&Jcol,FALSE))
                                        = FORMULA(Iloc+3,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))                    =SET.NAME("RowHed",-1)
                                        = NEXT()                                                  =     END.IF()               =SET.NAME("RowFst",GET.DOCUMENT(9))
                                                                                                  = NEXT()                     =SET.NAME("RowLst",GET.DOCUMENT(10))
                                        ! Some data currently loaded                              = END.IF()                   =SET.NAME("ColFst",GET.DOCUMENT(11))
                                        =ELSE()                                                   =NEXT()                      =SET.NAME("ColLst",GET.DOCUMENT(12))
                                        = RUN(SMS_GetWorksheet,FALSE)
                                                                                                  ! Format                     ! Check for (essentially) blank sheet
                                        ! Check if this data already loaded                                                    =IF(MIN(ColLst-ColFst,RowLst-RowFst)<0.5)
                                                                                                  =SELECT(INDIRECT("'"&S0dat&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                        = ECHO(FALSE)                                             =COLUMN.WIDTH(0.5)           = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Worksheet ("&S1dat&") is effectively empty.")
                                                                                                  =COLUMN.WIDTH(,,,3)
                                        = SET.NAME("Atitle",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&1,FALSE)))               = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                        = IF(NOT(ISERROR(FIND(S1dat,Atitle))))                                                 = ALERT("This
                                                                                                  =FORMULA.GOTO(Arow&1&Acol&1,TRUE) worksheet contains at most "&ColLst-ColFst+1&" columns and "&RowLst-RowFst+1&" rows of information."&Achr&Achr&"Specify a sheet that contains at least two STR profiles and t
                                        = ECHO(TRUE)                                                                           = ACTIVATE(S0con)
                                                                                                  ! Close the old file         = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                        = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > That worksheet ("&S1dat&") is already loaded.")
                                        = ACTIVATE(S0con)                                         =ACTIVATE(S1dat)             = FORMULA.GOTO(Arow&IF(Nloc<1.5,Cbut2row,Cbut3row)&Acol&1,FALSE)
                                        = FORMULA.GOTO(Arow&1&Acol&1,TRUE)                        =CLOSE(FALSE)                = ECHO(TRUE)
                                        = FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALSE)                                             = HALT()
                                                                                                  ! Update the worksheet list =END.IF()
                                        = ALERT("The worksheet ("&S1dat&") is already loaded."&Achr&"The current dataset is: "&Atitle&"."&Achr&Achr&"Specify a different worksheet and try again.",2)



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                          11/18/2010 6:23 PM                                                                                                                                     15 / 38
Built by: DL Duewer, NIST
                                                                                                                           STR_MatchSamples <Version 2-Jun-09>                                                                                                                               Paid for, in part, by: NIJ

                                                                                                             A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                    = HALT()                                                   =ACTIVATE(S0con)
                                                    = END.IF()                                                                             ! Find the header row/col
                                                                                                               =SET.NAME("Nfile",COUNTA(INDIRECT("'"&S0con&"'!"&Acol&Ched3col,FALSE)))
                                                                                                                                           =FOR("Irow",RowFst,RowLst,1)
                                                                                                               =FORMULA(RowLst-RowHed,INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col,FALSE))
                                                    ! Get the current attitude towards new loci                                            = SET.NAME("Atmp1",COUNTA(INDIRECT("'"&S1dat&"'!"&Arow&Irow,FALSE)))
                                                                                                               =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col+1,FALSE))
                                                    ! 1 = no new ones                                                                      = IF(Atmp1>1.5)
                                                                                                               =FORMULA(S1dat,INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col+2,FALSE))
                                                    ! 2 = all new ones                                                                     = FOR("Icol",ColFst,ColLst,1)
                                                                                                               =FORMULA(Dir1,INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col+3,FALSE))
                                                    ! 3 = only confirmed new ones                                                          =     SET.NAME("Atmp2",COUNTA(INDIRECT("'"&S1dat&"'!"&Acol&Icol,FALSE)))
                                                                                                               =SET.NAME("Nloc",COUNTA(INDIRECT("'"&S0wrk&"'!"&Acol&3,FALSE)))
                                                    = SET.NAME("AddTyp",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Cbut2row&Acol&1,FALSE)))        =     IF(Atmp2>1.5)
                                                                                                               =FORMULA(Nloc,INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col+4,FALSE))
                                                                                                                                           =      SET.NAME("Atmp3",DEREF(INDIRECT("'"&S1dat&"'!"&Arow&Irow&Acol&Icol,FALSE)))
                                                    = IF(ISERROR(AddTyp*1),SET.NAME("Addtyp",1),IF(OR(AddTyp<0.5,AddTyp>3.5),SET.NAME("Addtyp",1)))
                                                                                                               =FOR("Iloc",2,Nloc,1)
                                                    = FORMULA(AddTyp,INDIRECT("'"&S0con&"'!"&Arow&Cbut2row&Acol&1,FALSE))
                                                                                                                                           ! First significant row/col begins "SAMPLECODE", found header
                                                                                                               = SET.NAME("Aloc",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc&Acol&3,FALSE)))
                                                    ! Classify the loci of the new file                                                    =      IF(UPPER(LEFT(Atmp3,10))="SAMPLECODE")
                                                                                                               = FORMULA(Aloc,INDIRECT("'"&S0con&"'!"&Arow&1+Nfile&Acol&Ched3col+Iloc+3,FALSE))
                                                                                                               =NEXT()
                                                    = FORMULA("OutCol",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&6,FALSE))                       =        SET.NAME("RowHed",Irow)
                                                                                                               =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched3col&Asep&Acol&256,FALSE))
                                                    = FORMULA("Status",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&7,FALSE))                       =        SET.NAME("ColHed",Icol)
                                                                                                               =COLUMN.WIDTH(,,,3)
                                                    = SET.NAME("Nloc",COUNTA(INDIRECT("'"&S0wrk&"'!"&Acol&3,FALSE))-1)                     =        SET.NAME("Icol",ColLst+1)
                                                                                                                                           =
                                                    = FORMULA.ARRAY("",INDIRECT("'"&S0wrk&"'!"&Arow&2&Acol&6&Asep&Arow&Nloc+1&Acol&7,FALSE))        SET.NAME("Irow",RowLst+1)
                                                                                                               =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                    = SET.NAME("Nprv",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&4,FALSE))-2)
                                                    = SET.NAME("Nold",0)                                       =RETURN()                   ! Whoops, format doesn't appear to be NIST format (standard or compact)
                                                    = SET.NAME("Nnew",0)                                                                   =      ELSE()
                                                    = FOR("Iloc",1,Nloc,1)                                                                 =        MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Worksheet not in NIST format.")
                                                    = SET.NAME("Aloc",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&3,FALSE)))            =        ACTIVATE(S1dat)
                                                    = SET.NAME("Atmp1",MATCH(Aloc,INDIRECT("'"&S0dat&"'!"&Arow&4,FALSE),0)) =                       FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                    = IF(NOT(ISERROR(Atmp1)))                                                              =        FORMULA.GOTO(Arow&MAX(1,RowHed)&Acol&MAX(1,ColHed),FALSE)
                                                    =     SET.NAME("Nold",Nold+1)                                                          =        ECHO(TRUE)
                                                    =     FORMULA(Atmp1,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))                 =        ALERT("The header row does not begin with the keyword 'SampleCode'."&Achr&"This worksheet thus does not appear to be in either the NIST standard or compact format."&Achr&Achr&"Ple
                                                    =     FORMULA("aaOld",INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))               =        HALT()
                                                    = ELSE()                                                                               =      END.IF()
                                                    =     SET.NAME("Nnew",Nnew+1)                                                          =     END.IF()
                                                    =     FORMULA(Nloc+Nnew+3,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE)) NEXT()    =
                                                    =     FORMULA("New",INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))                 = END.IF()
                                                    = END.IF()                                                                             =NEXT()
                                                    = NEXT()
                                                                                                                                           ! Find the last non-blank row
                                                    ! No old loci specified                                                                =FOR("Irow",RowLst,RowHed+1,-1)
                                                    = IF(Nold<0.5)                                                                         = SET.NAME("Ntmp",COUNTA(INDIRECT("'"&S1dat&"'!"&Arow&Irow,FALSE)))
                                                    =                                                                                      = any loci with
                                                          MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > The 'Added' do not share IF(Ntmp>0.5)previously 'Add'ed datasets.")
                                                    =     ACTIVATE(S1dat)                                                                  = SET.NAME("RowLst",Irow)
                                                    =     FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                 = SET.NAME("Irow",RowHed)
                                                    =                                                                                      = END.IF()
                                                          SELECT(INDIRECT("'"&S1dat&"'!"&Arow&RowHed&Acol&ColHed+1&Asep&Arow&RowHed&Acol&ColLst,FALSE))
                                                    =     ECHO(TRUE)                                                                       =NEXT()
                                                    =     IF(AddTyp=1)
                                                    =                                                                                      ! Find the first blank col
                                                           ALERT("The 'Add'ed data ("&S1dat&") does not share any loci with the prior datasets."&Achr&Achr&"Correct loci designations or change the 'Use Loci' option setting."&Achr&"Then retry 'Add Data'.",2)
                                                    =      HALT()                                                                          =FOR("Icol",ColHed+1,ColLst,1)
                                                    =     ELSE()                                                                           = SET.NAME("Ntmp",COUNTA(INDIRECT("'"&S1dat&"'!"&Acol&Icol,FALSE)))
                                                    =                                                                                      = IF(Ntmp<0.5)
                                                           SET.NAME("Atmp1",ALERT("The 'Add'ed data ("&S1dat&") does not share any loci with the prior datasets."&Achr&Achr&"Click 'OK' to continue, 'Cancel' to stop and edit the loci designations.",1))
                                                    =      IF(NOT(Atmp1))                                                                  = SET.NAME("ColLst",Icol-1)
                                                    =        MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Returning to control = END.IF()to User.")
                                                    =        ALERT("Please correct the hassle and try again.",2)                           =NEXT()
                                                    =        HALT()
                                                    =      END.IF()                                                                        ! Clear the workspace
                                                    =     END.IF()                                                                         =ACTIVATE(S0wrk)
                                                    = END.IF()                                                                             =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&3&Asep&Acol&114,FALSE))
                                                                                                                                           =CLEAR(3)
                                                    ! Not all old loci specified                                                           =FORMULA("Locus",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&3,FALSE))
                                                    = IF(AND(Nold<Nprv-0.5,AddTyp=1))                                                      =FORMULA("#",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&4,FALSE))
                                                    = FOR("Iloc",1,Nprv,1)                                                                 =FORMULA("InCol",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&5,FALSE))
                                                    =     SET.NAME("Aloc",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&3+Iloc,FALSE)))        =FORMULA("OutCol",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&6,FALSE))
                                                    =     SET.NAME("Atmp1",MATCH(Aloc,INDIRECT("'"&S0wrk&"'!"&Acol&3,FALSE)))
                                                    =                                                                                        Find the the 'First' data loci. Missing: "&Aloc)
                                                          MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > The 'Added' data must !have all ofalleles
                                                    =     ACTIVATE(S0con)                                                                  =SET.NAME("Nloc",0)
                                                    =     FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                 =FOR("Icol",ColHed+1,ColLst,1)
                                                    =     FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALSE)                                         = SET.NAME("Aloc",CLEAN(TRIM(INDIRECT("'"&S1dat&"'!"&Arow&RowHed&Acol&Icol,FALSE))))
                                                    =     ACTIVATE(S1dat)                                                                  = SET.NAME("Itmp",MATCH(Aloc,INDIRECT("'"&S0wrk&"'!"&Acol&3,FALSE),0))
                                                    =     FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                    =                                                                                      ! Found a new one
                                                          SELECT(INDIRECT("'"&S0con&"'!"&Arow&RowHed&Acol&ColHed+1&Asep&Arow&RowHed&Acol&ColLst,FALSE))
                                                    =     ECHO(TRUE)                                                                       = IF(ISERROR(Itmp))
                                                    =                                                                                      = "&Aloc&"."&Achr&Achr&"Correct the spelling or change the 'Add Locus' option.",2)
                                                          ALERT("The Added data ("&S1dat&") does not specify at least one of the required loci: SET.NAME("Nloc",Nloc+1)
                                                    =     HALT()                                                                           = FORMULA(Aloc,INDIRECT("'"&S0wrk&"'!"&Arow&1+Nloc&Acol&3,FALSE))
                                                    = NEXT()                                                                               = FORMULA(1,INDIRECT("'"&S0wrk&"'!"&Arow&1+Nloc&Acol&4,FALSE))
                                                    = END.IF()                                                                             = FORMULA(Icol,INDIRECT("'"&S0wrk&"'!"&Arow&1+Nloc&Acol&5,FALSE))
                                                                                                                                           = ELSE.IF(Itmp<>Nloc+1)
                                                    ! Some new loci specified                                                              = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Alleles for each locus must be contiguous.")
                                                    = IF(Nnew>0.5)                                                                         = SET.NAME("Jtmp",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Itmp&Acol&5,FALSE)))



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                                  11/18/2010 6:23 PM                                                                                                                                          16 / 38
Built by: DL Duewer, NIST
                                                                                                                          STR_MatchSamples <Version 2-Jun-09>                                                                                                                             Paid for, in part, by: NIJ

                                                                                                            A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                    =   SET.NAME("Ntmp",Nprv)                                                        = ACTIVATE(S1dat)
                                                    =   FOR("Iloc",1,Nloc,1)                                                         = ECHO(TRUE)
                                                    =                                                                                =
                                                         SET.NAME("Aloc",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&3,FALSE))) SELECT("("&Arow&RowHed&Acol&Icol&Alsp&Arow&RowHed&Acol&Jtmp&")")
                                                    =                                                                                =
                                                         SET.NAME("Atyp",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))) ALERT("Alleles for each locus must be contiguous."&Achr&Achr&"Unless this is a test, this is unusual enough that you should suspect the integrity of these data.",2)
                                                    =    IF(Atyp="New")                                                              = HALT()
                                                    =     FORMULA(-1,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))              = ELSE()
                                                                                                                                     = SET.NAME("Iall",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&1+Nloc&Acol&4,FALSE)))
                                                    ! Ask whether to keep or ignore                                                  = FORMULA(Iall+1,INDIRECT("'"&S0wrk&"'!"&Arow&1+Nloc&Acol&4,FALSE))
                                                    =     IF(AddTyp>2.5)                                                             = END.IF()
                                                    =       MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > New locus: "&Aloc) =NEXT()
                                                    =       ACTIVATE(S1dat)
                                                    =       FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                         =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                    =                                                                                =RETURN()
                                                            SELECT(INDIRECT("'"&S1dat&"'!"&Arow&RowHed&Acol&ColHed+1&Asep&Arow&RowHed&Acol&ColLst,FALSE))
                                                    =       ECHO(TRUE)
                                                    =       SET.NAME("Atmp1",ALERT("Locus("&Aloc&") was not previously specified."&Achr&Achr&"Click 'OK' to add it, 'Cancel' to skip it.",1))
                                                    =       IF(Atmp1)
                                                    =        SET.NAME("Ntmp",Ntmp+1)
                                                    =        FORMULA(Aloc,INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Ntmp+3,FALSE))
                                                    =        FORMULA(Ntmp+3,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))
                                                    =        FORMULA("Added",INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))
                                                    =       END.IF()
                                                    =       ECHO(FALSE)

                                                    ! Accept it
                                                    =     ELSE()
                                                    =      SET.NAME("Ntmp",Ntmp+1)
                                                    =      FORMULA(Aloc,INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Ntmp+3,FALSE))
                                                    =      FORMULA(Ntmp+3,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))
                                                    =      FORMULA("Added",INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))
                                                    =     END.IF()
                                                    =    END.IF()
                                                    = NEXT()

                                                    ! Take care of reformating the locus header




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                                 11/18/2010 6:23 PM                                                                                                                                        17 / 38
Built by: DL Duewer, NIST
                                                                                                                          STR_MatchSamples <Version 2-Jun-09>                                                                                         Paid for, in part, by: NIJ

                                                                                                            A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                    = IF(Ntmp>Nprv)
                                                    =   ACTIVATE(S0dat)
                                                    =   SELECT(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Nprv+4&Asep&Arow&4&Acol&Ntmp+3,FALSE))
                                                    =   BORDER(1)
                                                    =   COLUMN.WIDTH(,,,3)
                                                    =   SELECT(INDIRECT("'"&S0dat&"'!"&Arow&3&Acol&4&Asep&Arow&3&Acol&Ntmp+3,FALSE))
                                                    =   ALIGNMENT(7,FALSE,3,0)
                                                    = END.IF()
                                                    = END.IF()
                                                    =END.IF()

                                                    ! Sort the data
                                                    =ECHO(FALSE)
                                                    =ACTIVATE(S0wrk)
                                                    =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&3&Asep&Acol&7,FALSE))
                                                    =SORT(1,Arow&2&Acol&7,1,Arow&2&Acol&6,1,,,1)
                                                    =ACTIVATE(S0con)
                                                    =ECHO(TRUE)
                                                    =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                    =FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALSE)

                                                    ! Get the data
                                                    =RUN(SMS_GetData,FALSE)

                                                    ! Update the data counts
                                                    =SET.NAME("Atitle",IF(NsamOld<1.5,S1dat,Atitle&", "&S1dat))
                                                    =FORMULA(Atitle,INDIRECT("'"&S0dat&"'!"&Arow&1&Acol&1,FALSE))
                                                    =FORMULA("Loaded",INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col,FALSE))
                                                    =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col+1,FALSE))
                                                    =RUN(SMS_DataUpdate,FALSE)

                                                    =ECHO(TRUE)
                                                    =FORMULA.GOTO(Arow&Cbut2row&Acol&1,FALSE)
                                                    =ALERT("Data from "&S1dat&" has been loaded."&Achr&"This data has NOT been validated!"&Achr&Achr&"You should check the Types, sample labels, and all of the allele lists before proceeding.",2)

                                                    =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                    =HALT()




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                                  11/18/2010 6:23 PM                                                                                                   18 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               19 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               20 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               21 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                                                           Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                             SMS_ReadMe2               SMS_Report             SMS_RepDetail
                                                                                       ! Generate the reports
                                                             ! Take user to the ReadMe worksheet, no nonsence ! The detailed report
                                                                                       =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                                                             =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                                                             =ECHO(FALSE)              =ECHO(FALSE)           =CANCEL.KEY(TRUE,SMS_Cancel)

                                                             =ACTIVATE(S0rme)     ! Have the data been checked?! Update Control Pannel
                                                                                  =ACTIVATE(S0con)
                                                             =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                 =ECHO(FALSE)
                                                                                                               =ACTIVATE(S0con)
                                                                                  =SET.NAME("Atmp",INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched1col,FALSE))
                                                                                  =IF(NOT(Atmp="Checked")) > Finished!")
                                                             =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&"=FORMULA("Processing",INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched1col,FALSE))
                                                             =RETURN()            = ACTIVATE(S0con)            =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched1col+1,FALSE))
                                                                                                               =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,FALSE))
                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > You must first 'Check' the data.")
                                                                                                               =COLUMN.WIDTH(,,,3)
                                                                                  = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                               =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                  = FORMULA.GOTO(Arow&Cbut3row&Acol&1,FALSE)
                                                                                  = ECHO(TRUE)                 =ECHO(TRUE)
                                                                                                               =FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)
                                                                                  = ALERT("You must first 'Check' the data."&Achr&Achr&"Please click the button, fix any problems, and try again.",2)
                                                                                  = HALT()                     =ECHO(FALSE)
                                                                                  =END.IF()
                                                                                                               ! Make report
                                                                                  ! Update the data counts =SET.NAME("Icol0",4)
                                                                                  =RUN(SMS_DataUpdate,FALSE)   =SET.NAME("Icol1",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&4,FALSE))+1)
                                                                                                               =SET.NAME("Nloc",Icol1-Icol0+1)
                                                                                                               =SET.NAME("Nsam",DEREF(INDIRECT("'"&S0con&"'!"&Arow&3&Acol&Ched2col+1,FALSE)))
                                                                                  ! Match to all or only to unmatched?
                                                                                                               =SET.NAME("Nexr",DEREF(INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched2col+1,FALSE)))
                                                                                  =SET.NAME("Nums",DEREF(INDIRECT("'"&S0con&"'!"&Arow&2&Acol&10,FALSE)))
                                                                                                               =SET.NAME("Nums",DEREF(INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched2col+1,FALSE)))
                                                                                  =SET.NAME("IsAll",DEREF(INDIRECT("'"&S0con&"'!"&Arow&Cbut4row&Acol&1,FALSE)))
                                                                                                               =IF(Nums<1.5)
                                                                                  =IF(IsAll<>2,SET.NAME("IsAll",1))
                                                                                  =IF(AND(IsAll=2,Nums<0.5)) = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > No unmatched samples!")
                                                                                  = ACTIVATE(S0con)            = FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)
                                                                                                               = ECHO(TRUE)
                                                                                  = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > There are no 'UnMatched' samples!")
                                                                                                               = ALERT("There
                                                                                  = FORMULA.GOTO(Arow&1&Acol&1,TRUE) are only "&Nums&" 'UnMatch' samples."&Achr&Achr&"The "&S0dat&" worksheet may well provide all the interesting information."&Achr&
                                                                                                               = HALT()
                                                                                  = FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)
                                                                                  = ECHO(TRUE)                 =END.IF()
                                                                                  = ALERT("There are no 'Unmatched' samples. Unless you wish to match against 'All Samples', you do not need any further report.",2)
                                                                                  = HALT()
                                                                                  =END.IF()                    =SET.NAME("Irow0",5)
                                                                                                               =SET.NAME("Irow1",Irow0+Nums-1)
                                                                                  ! Clear any old reports      =SET.NAME("Irow2",IF(IsAll=2,Irow1,Irow0+Nsam-1))
                                                                                  =FOR("Itmp",1,Nrepsht,1)     =SET.NAME("Nlne",MIN(Nlne0,Nums))
                                                                                  = SET.NAME("Atmp",INDIRECT("'"&S0con&"'!"&Arow&3+Itmp&Acol&Ched1col,FALSE))
                                                                                  = IF(NOT(Atmp="Clear")) ! Format the header lines
                                                                                                               =ACTIVATE(S0rp1)
                                                                                  = SET.NAME("S0rep",EVALUATE("S0rp"&Itmp))
                                                                                                               =SELECT(INDIRECT("'"&S0rp1&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                  = SET.NAME("Arep",EVALUATE("S0rp"&Itmp&"_txt"))
                                                                                                               =CLEAR(1)
                                                                                  = FORMULA("Clear",INDIRECT("'"&S0con&"'!"&Arow&Itmp+3&Acol&Ched1col,FALSE))
                                                                                                               =FORMAT.NUMBER("0")
                                                                                  = FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&Itmp+3&Acol&Ched1col+1,FALSE))
                                                                                                               =FONT.PROPERTIES("Arial","Regular",10,,,,,,,1)
                                                                                  = FORMULA(MID(S0rep,FIND("]",S0rep)+1,100),INDIRECT("'"&S0con&"'!"&Arow&Itmp+3&Acol&Ched1col+2,FALSE))
                                                                                                               =ALIGNMENT(4,FALSE,2)
                                                                                  = FORMULA(Arep&IF(IsAll=1,Amat1_txt,Amat2_txt),INDIRECT("'"&S0con&"'!"&Arow&Itmp+2&Acol&Ched1col+3,FALSE))
                                                                                  = ACTIVATE(S0rep)            =FREEZE.PANES(FALSE)
                                                                                                               =FREEZE.PANES(TRUE,,3)
                                                                                  = SELECT(INDIRECT("'"&S0rep&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                  = EDIT.DELETE()
                                                                                  = COLUMN.WIDTH(0.5) =FORMULA(S0rp1_txt&IF(IsAll=1,Amat1_txt,Amat2_txt),INDIRECT("'"&S0rp1&"'!"&Arow&1&Acol&1,FALSE))
                                                                                                               =SELECT(INDIRECT("'"&S0rp1&"'!"&Arow&1&Acol&1&Asep&Arow&1&Acol&Nlne*(Nblock+1)-1,FALSE))
                                                                                  = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                  = END.IF()                   =FONT.PROPERTIES("Arial","Bold",12,,,,,,,1)
                                                                                  =NEXT()                      =ALIGNMENT(7,FALSE,3,0,FALSE)
                                                                                                               =FOR("Ilne",1,Nlne,1)
                                                                                  ! Check Cutoffs              = FORMULA("Sample From",INDIRECT("'"&S0rp1&"'!"&Arow&3&Acol&1+(Ilne-1)*(Nblock+1),FALSE))
                                                                                  =RUN(SMS_CheckCutoffs,FALSE) = FORMULA("Sample To",INDIRECT("'"&S0rp1&"'!"&Arow&3&Acol&2+(Ilne-1)*(Nblock+1),FALSE))
                                                                                                               = FORMULA("%Allele",INDIRECT("'"&S0rp1&"'!"&Arow&3&Acol&3+(Ilne-1)*(Nblock+1),FALSE))
                                                                                  ! Tell the user              = FORMULA("%Loci:1+",INDIRECT("'"&S0rp1&"'!"&Arow&3&Acol&4+(Ilne-1)*(Nblock+1),FALSE))
                                                                                  =ACTIVATE(S0con)             = SELECT(INDIRECT("'"&S0rp1&"'!"&Arow&3&Acol&1+(Ilne-1)*(Nblock+1)&Asep&Arow&3&Acol&4+(Ilne-1)*(Nblock+1),FALSE))
                                                                                                               = ALIGNMENT(3)
                                                                                  =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,FALSE))
                                                                                  =COLUMN.WIDTH(,,,3)          = BORDER(1)
                                                                                                               = SELECT(INDIRECT("'"&S0rp1&"'!"&Acol&3+(Ilne-1)*(Nblock+1)&Asep&Acol&4+(Ilne-1)*(Nblock+1),FALSE))
                                                                                  =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                  =ECHO(TRUE)                  = FORMAT.NUMBER("0.0")
                                                                                                               =NEXT()
                                                                                  =FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)
                                                                                  =ECHO(FALSE)                 =ECHO(TRUE)
                                                                                                               =SELECT(INDIRECT("'"&S0rp1&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                  ! Make the summary           =COLUMN.WIDTH(0.5)
                                                                                  =RUN(SMS_RepDetail,FALSE)    =COLUMN.WIDTH(,,,3)
                                                                                  =RUN(SMS_RepSummary,FALSE)   =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                  =RUN(SMS_RepProfile,FALSE)   =ECHO(FALSE)

                                                                                      ! Format the ControlPanel ! Format the worksheet
                                                                                      =ACTIVATE(S0con)          =ACTIVATE(S0wrk)
                                                                                                                =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&3&Asep&Acol&256,FALSE))
                                                                                      =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,FALSE))



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                   11/18/2010 6:23 PM                                                                                                                            22 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                                                     Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                     =COLUMN.WIDTH(0.5)   =CLEAR(3)
                                                                                     =COLUMN.WIDTH(,,,3)  =FORMAT.NUMBER("0")
                                                                                                          =FONT.PROPERTIES("Arial","Regular",10,,,,,,,1)
                                                                                     =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                     =ECHO(TRUE)          =ALIGNMENT(4,FALSE,2)
                                                                                                          =FORMULA("Locus",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&6,FALSE))
                                                                                     =FORMULA.GOTO(Arow&Cbut5row&Acol&1,FALSE)
                                                                                                          =FORMULA("#",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&7,FALSE))
                                                                                                          =FOR("Itmp",1,20,1)
                                                                                     =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                     =HALT()              = FORMULA("All-"&TEXT(Itmp,"00"),INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&7+Itmp,FALSE))
                                                                                                          = FORMULA("Bll-"&TEXT(Itmp,"00"),INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&57+Itmp,FALSE))
                                                                                                          =NEXT()
                                                                                                          =FOR("Iloc",1,Nloc,1)
                                                                                                          = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&Iloc+Icol0-1,FALSE)))
                                                                                                          = FORMULA(Atmp,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&6,FALSE))
                                                                                                          =NEXT()
                                                                                                          =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                                          =COLUMN.WIDTH(0.5)
                                                                                                          =COLUMN.WIDTH(,,,3)
                                                                                                          =FORMULA.GOTO(Arow&1&Acol&1,TRUE)

                                                                                                             ! Loop over all sample pairs
                                                                                                             =SET.NAME("Ilne",Nlne+1)
                                                                                                             =SET.NAME("Krow",0)
                                                                                                             =SET.NAME("Rout",4)
                                                                                                             =FOR("Irow",Irow0,Irow1,1)
                                                                                                             = ACTIVATE(S0wrk)
                                                                                                             = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Processing... "&Irow-Irow0+1&" / "&Nums)
                                                                                                             = SELECT(INDIRECT("'"&S0wrk&"'!"&Arow&2&Acol&7&Asep&Arow&Nloc+1&Acol&56,FALSE))
                                                                                                             = CLEAR(3)
                                                                                                             = SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&116&Asep&Acol&120,FALSE))
                                                                                                             = CLEAR(3)

                                                                                                             = SET.NAME("Asam",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&2,FALSE)))
                                                                                                             = FORMULA(Asam,INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&116,FALSE))

                                                                                                             ! Fetch and store individual alleles
                                                                                                             = SET.NAME("Mall1",0)
                                                                                                             = SET.NAME("Mlps1",0)
                                                                                                             = FOR("Iloc",1,Nloc,1)
                                                                                                             = SET.NAME("Nall",0)
                                                                                                             = IF(NOT(ISBLANK(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Iloc+Icol0-1,FALSE))))
                                                                                                             =    SET.NAME("Anew",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&Iloc+Icol0-1,FALSE)))
                                                                                                             =    SET.NAME("Itmp",0)
                                                                                                             =    WHILE(NOT(ISERROR(Itmp)))
                                                                                                             =     SET.NAME("Itmp",FIND(Alsp,Anew))
                                                                                                             =     IF(ISERROR(Itmp))
                                                                                                             =       SET.NAME("Nall",Nall+1)
                                                                                                             =       FORMULA(Anew,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7+Nall,FALSE))
                                                                                                             =     ELSE()
                                                                                                             =       SET.NAME("Nall",Nall+1)
                                                                                                             =       FORMULA(LEFT(Anew,Itmp-1),INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7+Nall,FALSE))
                                                                                                             =       SET.NAME("Anew",MID(Anew,Itmp+1,100))
                                                                                                             =     END.IF()
                                                                                                             =    NEXT()
                                                                                                             = END.IF()
                                                                                                             = FORMULA(Nall,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE))
                                                                                                             = IF(Nall>0.5)
                                                                                                             =    SET.NAME("Mall1",Mall1+Nall)
                                                                                                             =    SET.NAME("Mlps1",Mlps1+1)
                                                                                                             = END.IF()
                                                                                                             = NEXT()
                                                                                                             = FORMULA("#Alleles: "&Mall1,INDIRECT("'"&S0wrk&"'!"&Arow&2&Acol&116,FALSE))
                                                                                                             = FORMULA("#Loci: "&Mlps1,INDIRECT("'"&S0wrk&"'!"&Arow&3&Acol&116,FALSE))

                                                                                                             ! Loop over all other samples
                                                                                                             = FOR("Jrow",Irow0,Irow2,1)
                                                                                                             = IF(Jrow<>Irow)
                                                                                                             =    SELECT(INDIRECT("'"&S0wrk&"'!"&Arow&2&Acol&58&Asep&Arow&Nloc+1&Acol&106,FALSE))
                                                                                                             =    CLEAR(3)
                                                                                                             =    SET.NAME("Asam",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&2,FALSE)))
                                                                                                             =    FORMULA(Asam,INDIRECT("'"&S0wrk&"'!"&Arow&Jrow-Irow0+1&Acol&117,FALSE))
                                                                                                             =    SET.NAME("Mall2",0)
                                                                                                             =    SET.NAME("Mlps2",0)




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                                                       23 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                                                     Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                           ! Disperse the target alleles
                                                                                                           =    FOR("Iloc",1,Nloc,1)
                                                                                                           =     IF(NOT(ISBLANK(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&Iloc+Icol0-1,FALSE))))
                                                                                                           =      SET.NAME("Anew",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&Iloc+Icol0-1,FALSE)))
                                                                                                           =      SET.NAME("Nall",0)
                                                                                                           =      SET.NAME("Itmp",0)
                                                                                                           =      WHILE(NOT(ISERROR(Itmp)))
                                                                                                           =        SET.NAME("Itmp",FIND(Alsp,Anew))
                                                                                                           =        IF(ISERROR(Itmp))
                                                                                                           =         SET.NAME("Nall",Nall+1)
                                                                                                           =         FORMULA(Anew,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&57+Nall,FALSE))
                                                                                                           =        ELSE()
                                                                                                           =         SET.NAME("Nall",Nall+1)
                                                                                                           =         FORMULA(LEFT(Anew,Itmp-1),INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&57+Nall,FALSE))
                                                                                                           =         SET.NAME("Anew",MID(Anew,Itmp+1,100))
                                                                                                           =        END.IF()
                                                                                                           =      NEXT()
                                                                                                           =     END.IF()
                                                                                                           =    NEXT()

                                                                                                           ! Count the alleles in common
                                                                                                           =    FOR("Iloc",1,Nloc,1)
                                                                                                           =     SET.NAME("Nall",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7,FALSE)))
                                                                                                           =     IF(Nall>0.5)
                                                                                                           =      SET.NAME("Itmp",0)
                                                                                                           =      FOR("Iall",1,Nall,1)
                                                                                                           =        SET.NAME("Atmp",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&7+Iall,FALSE)))
                                                                                                           =        IF(NOT(ISERROR(MATCH(Atmp,INDIRECT("'"&S0wrk&"'!"&Arow&Iloc+1&Acol&58&Asep&Arow&Iloc+1&Acol&106,FALSE),0))),SET.NAME("Itmp
                                                                                                           =      NEXT()
                                                                                                           =      SET.NAME("Mall2",Mall2+Itmp)
                                                                                                           =      IF(Itmp>0.5,SET.NAME("Mlps2",Mlps2+1))
                                                                                                           =     END.IF()
                                                                                                           =    NEXT()
                                                                                                           =    FORMULA(100*Mall2/Mall1,INDIRECT("'"&S0wrk&"'!"&Arow&Jrow-Irow0+1&Acol&118,FALSE))




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                                                       24 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                                                                                                   Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                                                                                           =   FORMULA(100*Mlps2/Mlps1,INDIRECT("'"&S0wrk&"'!"&Arow&Jrow-Irow0+1&Acol&119,FALSE))
                                                                                                           = END.IF()
                                                                                                           = NEXT()

                                                                                                           ! Sort the best ones to the top
                                                                                                           = SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&117&Asep&Acol&119,FALSE))
                                                                                                           = SORT(1,Arow&1&Acol&119,2,Arow&1&Acol&118,2,,,2)

                                                                                                           ! Define "Best"
                                                                                                           = SET.NAME("Nbst",COUNTA(INDIRECT("'"&S0wrk&"'!"&Acol&119,FALSE)))
                                                                                                           = IF(LkepMax,SET.NAME("Nbst",MIN(Nbst,NkepMax)))
                                                                                                           = IF(LkepMin)
                                                                                                           = SET.NAME("Itmp",MATCH(PkepMin,INDIRECT("'"&S0wrk&"'!"&Acol&119,FALSE),-1))
                                                                                                           = IF(ISERROR(Itmp),SET.NAME("Itmp",0))
                                                                                                           = SET.NAME("Nbst",IF(LkepMax,MIN(Nbst,Itmp),Itmp))
                                                                                                           = END.IF()
                                                                                                           = SET.NAME("Nbst",MAX(Nbst,3))

                                                                                                           ! Keep the best
                                                                                                           = SET.NAME("Ilne",Ilne+1)
                                                                                                           = IF(Ilne>Nlne+0.5)
                                                                                                           = ACTIVATE(S0rp1)
                                                                                                           = ECHO(TRUE)
                                                                                                           = FORMULA.GOTO(Arow&Rout&Acol&1,TRUE)
                                                                                                           = ECHO(FALSE)
                                                                                                           = SET.NAME("Ilne",1)
                                                                                                           = IF(Krow>0,SET.NAME("Rout",Rout+Nkep+1))
                                                                                                           = SET.NAME("Krow",Krow+1)
                                                                                                           = SET.NAME("Nkep",Nbst)
                                                                                                           = END.IF()
                                                                                                           = SET.NAME("Nkep",MAX(Nkep,Nbst))
                                                                                                           = SET.NAME("Cout",1+(Ilne-1)*(Nblock+1))

                                                                                                           ! Store the position info
                                                                                                           = FORMULA(Nbst,INDIRECT("'"&S0wrk&"'!"&Arow&Irow-Irow0+1&Acol&131,FALSE))
                                                                                                           = FORMULA(Rout,INDIRECT("'"&S0wrk&"'!"&Arow&Irow-Irow0+1&Acol&132,FALSE))
                                                                                                           = FORMULA(Cout,INDIRECT("'"&S0wrk&"'!"&Arow&Irow-Irow0+1&Acol&133,FALSE))

                                                                                                           ! Store the best
                                                                                                           = COPY(INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&116&Asep&Arow&Nbst&Acol&119,FALSE),INDIRECT("'"&S0rp1&"'!"&Arow&Rout&Acol&Cout,FALSE))
                                                                                                           =NEXT()

                                                                                                           ! Format
                                                                                                           =ACTIVATE(S0rp1)
                                                                                                           =SELECT(INDIRECT("'"&S0rp1&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                                                                                           =COLUMN.WIDTH(0.5)
                                                                                                           =COLUMN.WIDTH(,,,3)
                                                                                                           =FORMULA.GOTO(Arow&1&Acol&1,TRUE)

                                                                                                           =ACTIVATE(S0con)
                                                                                                           =ECHO(TRUE)
                                                                                                           =FORMULA("Finished",INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched1col,FALSE))
                                                                                                           =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&4&Acol&Ched1col+1,FALSE))

                                                                                                           =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                           =RETURN()




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                                                                                                     25 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               26 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               27 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               28 / 38
Built by: DL Duewer, NIST
                                                                                                                    STR_MatchSamples <Version 2-Jun-09>                                                                                                                          Paid for, in part, by: NIJ

                                                                                                      A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                 SMS_RepProfile               SMS_RepSummary            Utility_DateTimeDependencies Utility_CleanUpNames       Atmp1                 Utility_ReVersion      Utility_ShowMacro Utility_AlignControls Utility_Colors            1            1
                                 The profile report           The summary report                                     ! setting                  Atmp2
                                                                                        ! Check the DateTime format Get rid of all the transcients                    ! Reset Page Headers ! Unide allfiles
                                                                                                                                                                                             of visible hidden sheets                      ! List in colors    2
                                                                                                                                                                                                                    ! Make sure the buttons arethethe right spot            2
                                                              =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                                 =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) =MESSAGE(TRUE,R1C)                                               Atmp3                                        =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                                                                                                                                                                      =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)                        =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C)
                                                                                                                                                                                                                                                               3
                                                                                                                                                                                                                    =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C) 3
                                 =CANCEL.KEY(TRUE,SMS_Cancel)                           =ECHO(FALSE)
                                                              =CANCEL.KEY(TRUE,SMS_Cancel)                                                      AutoCall_Exit
                                                                                                                     =SET.NAME("Atmp1",COUNTA(NAMES()))               =ECHO(FALSE)           =ECHO(FALSE)           =ECHO(FALSE)                               4            4
                                                                                                                     =FOR("Atmp2",Atmp1,1,-1) AutoCall_Globals        =RUN(AutoCall_Globals,FALSE)                                         =ACTIVATE(S0mac) 5
                                                                                                                                                                                                                    =RUN(AutoCall_Globals,FALSE)                            5
                                 ! Update Control Pannel      ! Update Control Pannel                                = SET.NAME("Atmp3",INDEX(NAMES(),Atmp2))
                                                                                        ! Make sure that the chart isn't active                 AutoCall_StartUp                             ! Unhide                                      =FOR("Itmp",1,56,1) 6            6
                                 =ECHO(FALSE)                 =ECHO(FALSE)              =ACTIVATE(S0dti)             = IF(ISERROR(MATCH(Atmp3,C[1],0)),DELETE.NAME(Atmp3))
                                                                                                                                                SMS_Cancel                                    System for Matching Samplesthe sheets Worksheets by Their STR Profiles") 7
                                                                                                                                                                      =SET.NAME("Atmp","A=ERROR(FALSE)                                                         7
                                                                                                                                                                                                                    ! Clear Across Excel = FORMULA(Itmp,INDIRECT("'"&S0mac&"'!R"&Itmp&"C[1]",FALSE))
                                 =ACTIVATE(S0con)             =ACTIVATE(S0con)          =ERROR(FALSE)                =NEXT()                    SMS_ChangeColors                             = WORKBOOK.UNHIDE(MID(S0dti,FIND("]",S0dti)+1,100))
                                                                                                                                                                                                                    =RUN(SMS_Clear,FALSE)  = SELECT(INDIRECT("'"&S0mac&"'!R"&Itmp&"C[1]",FALSE))
                                                                                                                                                                                                                                                               8            8
                                                              =FORMULA("Processing",INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched1col,FALSE)) SMS_CheckCutoffs ! Reset Readme
                                                                                        = WORKBOOK.UNHIDE("DateTime")
                                 =FORMULA("Processing",INDIRECT("'"&S0con&"'!"&Arow&6&Acol&Ched1col,FALSE))                                                                                                                                = PATTERNS(1,1,Itmp,TRUE)
                                                                                                                                                                                             = WORKBOOK.UNHIDE(MID(S0mac,FIND("]",S0mac)+1,100))               9            9
                                                              =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched1col+1,FALSE)) SMS_CheckData
                                                                                        = SET.NAME("Itmp",FORMULA.GOTO(Arow&1&Acol&1,TRUE))
                                 =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&6&Acol&Ched1col+1,FALSE)) =RUN(AutoCall_Globals,FALSE)                                                                                 ! Hide the trivial worksheets
                                                                                                                                                                      =ACTIVATE(S0rme) = WORKBOOK.UNHIDE(MID(S0wrk,FIND("]",S0wrk)+1,100))                     10           10
                                                                                                                                                                                                                                           = SET.NAME("Jtmp",GET.CELL(63,INDIRECT("'"&S0mac&"'!R"&Itmp&"C[1]",FALSE)))
                                                              =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,FALSE))
                                                                                        = IF(ISERROR(Itmp))
                                 =SELECT(INDIRECT("'"&S0con&"'!"&Acol&Ched1col&Asep&Acol&Ched1col+1,FALSE))                                     SMS_Clear             =PAGE.SETUP("&L&""Arial,Regular""&8Built by: DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<"&Avers&">"&Atmp&"&R&""Arial,Regu
                                                                                                                                                                                             =ERROR(TRUE)           =ERROR(FALSE)                              11           11
                                                                                                                                                                                                                                           = FORMULA(Jtmp,INDIRECT("'"&S0mac&"'!R"&Itmp&"C[2]",FALSE))
                                 =COLUMN.WIDTH(,,,3)          =COLUMN.WIDTH(,,,3)       = HIDE()                     =RUN(AutoCall_StartUp,FALSE)
                                                                                                                                                SMS_Control           =FORMULA.GOTO(Arow&1&Acol&1,TRUE) = WORKBOOK.UNHIDE("DateTime")      =NEXT()             12           12
                                                              =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                 =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                      =END.IF()                                               SMS_DataAdd                                  ! format                                      =SELECT("C:C[1]")
                                                                                                                                                                                                                    = WORKBOOK.UNHIDE("Work")                  13           13
                                 =ECHO(TRUE)                  =ECHO(TRUE)               =ERROR(TRUE)                 =ACTIVATE(S0mac)           SMS_DataUpdate        ! Reset Introduction =ACTIVATE(S0mac) =ERROR(TRUE)                   =COLUMN.WIDTH(5) 14              14
                                                              =FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)
                                 =FORMULA.GOTO(Arow&Cbut4row&Acol&1,FALSE)                                                                      SMS_GetData
                                                                                                                     =FORMULA.GOTO(Arow&1&Acol,TRUE)                  =ACTIVATE(S0int)       =WINDOW.MAXIMIZE()     =ACTIVATE(S0dti)                           15           15
                                 =ECHO(FALSE)                 =ECHO(FALSE)              ! Get the current formats =HALT()                                                                     to "&Asyst&"!",INDIRECT("'"&S0int&"'!"&Arow&1&Acol&3,FALSE)) 16
                                                                                                                                                                                                                    =WORKBOOK.HIDE() =FORMULA.GOTO(Arow&1&Acol&"[-1]",TRUE)
                                                                                                                                                SMS_GetWorksheet =FORMULA("Welcome=OPTIONS.VIEW(TRUE,TRUE,FALSE,TRUE,1,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE)   16
                                                                                        =SET.NAME("Atmp",NOW())                                 SMS_Print                                    =FREEZE.PANES(FALSE)   =ACTIVATE(S0wrk)
                                                                                                                                                                      =FORMULA("Version <"&Avers&">",INDIRECT("'"&S0int&"'!"&Arow&2&Acol&3,FALSE))             17           17
                                 ! Make summary report        ! Make summary report     =FOR("Irow",2,4,1)                                      SMS_Print1            =PAGE.SETUP("&L&""Arial,Regular""&8Built by: DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<"&Avers&">"&Atmp&"&R&""Arial,Regu
                                                                                                                                                                                             =FREEZE.PANES(TRUE,0,1)=WORKBOOK.HIDE() =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                                                                                                                                                                                               18           18
                                 =SET.NAME("Icol0",4)                                   = FORMULA(Atmp,INDIRECT("'"&S0dti&"'!"&Arow&Irow&Acol&2,FALSE))
                                                              =SET.NAME("Nums",DEREF(INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched2col+1,FALSE)))    SMS_Print2            =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                                                                                                                                                                             =ZOOM(75)                                     =HALT()             19           19
                                                              =SET.NAME("Nlne",MIN(Nlne0,Nums))
                                                                                        = SET.NAME("Atmp1",GET.CELL(7,INDIRECT("'"&S0dti&"'!"&Arow&Irow&Acol&2,FALSE)))
                                 =SET.NAME("Icol1",COUNTA(INDIRECT("'"&S0dat&"'!"&Arow&4,FALSE))+1)                                             SMS_Print3                                                          ! Format the Introduction                  20           20
                                 =SET.NAME("Nloc",Icol1-Icol0+1)                                                                                SMS_ReadMe1           ! Reset ControlPanel =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                        = IF(NOT(ISERROR(FIND(";",Atmp1))),SET.NAME("Atmp1",LEFT(Atmp1,FIND(";",Atmp1)-1)))                                         =ACTIVATE(S0int)                           21           21
                                                              ! Format the header lines = IF(NOT(ISERROR(FIND("]",Atmp1))),SET.NAME("Atmp1",MID(Atmp1,FIND("]",Atmp1)+1,100)))
                                 =SET.NAME("Nums",DEREF(INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched2col+1,FALSE)))                                 SMS_ReadMe2           =ACTIVATE(S0con) =HALT()                      =SELECT("Button 4")                        22           22
                                                              =ACTIVATE(S0rp2)                                                                  SMS_RepDetail
                                                                                        = FORMULA(Atmp1,INDIRECT("'"&S0dti&"'!"&Arow&Irow&Acol&3,FALSE))                                                            =FORMAT.SIZE(239,25)                       23           23
                                                                                                                                                                      =PAGE.SETUP("&L&""Arial,Regular""&8Built by: DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<"&Avers&">"&Atmp&"&R&""Arial,Regu
                                                                                        = FORMULA("?",INDIRECT("'"&S0dti&"'!"&Arow&Irow&Acol&4,FALSE))
                                 ! Format the header lines =SELECT(INDIRECT("'"&S0rp2&"'!"&Acol&1&Asep&Acol&256,FALSE))                         SMS_Report            =FORMULA.GOTO(Arow&1&Acol&1,TRUE) =FORMAT.MOVE(1,1,Arow&28&Acol&4)                       24           24
                                 =ACTIVATE(S0rp3)             =CLEAR(1)                 =NEXT()                                                 SMS_RepProfile                                                      =SELECT("Button 3")                        25           25
                                                              =FORMAT.NUMBER("0")
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Acol&1&Asep&Acol&256,FALSE))                                                   SMS_RepSummary ! Reset Data worksheets                              =FORMAT.SIZE(239,37)                       26           26
                                 =CLEAR(1)                                              ! Check the Date format
                                                              =FONT.PROPERTIES("Arial","Regular",10,,,,,,,1)                                    Utility_CleanUpNames =ACTIVATE(S0dat)                               =FORMAT.MOVE(1,1,Arow&33&Acol&4)           27           27
                                 =FORMAT.NUMBER("0")                                                                                            Utility_DateTimeDependencies
                                                              =ALIGNMENT(4,FALSE,2) =SET.NAME("AlngDat",DEREF(INDIRECT("'"&S0dti&"'!"&Arow&2&Acol&3,FALSE)))                                                        =FORMULA.GOTO(Arow&1&Acol&1,TRUE) 28                    28
                                                                                                                                                                      =PAGE.SETUP("&L&""Arial,Regular""&8Built by: DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<"&Avers&">"&Atmp&"&R&""Arial,Regu
                                                              =FREEZE.PANES(FALSE) =SET.NAME("Itmp0",TEXT(NOW(),AlngDat))
                                 =FONT.PROPERTIES("Arial","Regular",10,,,,,,,1)                                                                 Utility_ReVersion     =FORMULA.GOTO(Arow&1&Acol&1,TRUE) =FORMULA.GOTO(Arow&29&Acol&4,FALSE)29                               29
                                 =ALIGNMENT(4,FALSE,2) =FREEZE.PANES(TRUE,,3) =IF(Itmp0=AlngDat,SET.NAME("Itmp0",NA()))                         Utility_ShowMacro                                                                                              30           30
                                 =FREEZE.PANES(FALSE)                                   =IF(ISERROR(Itmp0*1),SET.NAME("Itmp0",NA()))            Utility_AlignControls ! Do all the report sheets                    ! Take care of the ReadMe                  31           31
                                                                                        =IF(ISERROR(Itmp0))
                                 =FREEZE.PANES(TRUE,,4) =SELECT(INDIRECT("'"&S0rp2&"'!"&Acol&6&Asep&Acol&11,FALSE))                                                   =FOR("Itmp",1,Nrepsht,1)                      =ACTIVATE(S0rme)                           32           32
                                                              =FORMAT.NUMBER("0.0") = SET.NAME("NumNew",NumNew+1)                                                     = SET.NAME("S0rep",EVALUATE("S0rp"&Itmp))     =SELECT("Button 1")                        33           33
                                                              =FORMULA(S0rp2_txt&IF(IsAll=1,Amat1_txt,Amat2_txt),Arow&1&Acol&1)
                                                                                        = FORMULA("Invalid!",INDIRECT("'"&S0dti&"'!"&Arow&2&Acol&4,FALSE))
                                 =FORMULA(S0rp3_txt&IF(IsAll=1,Amat1_txt,Amat2_txt),Arow&1&Acol&1)                                                                    = ACTIVATE(S0rep)                             =FORMAT.SIZE(95,36)                        34           34
                                                              =SELECT(INDIRECT("'"&S0rp2&"'!"&Arow&1&Acol&1&Asep&Arow&1&Acol&11,FALSE))
                                                                                        = SELECT(INDIRECT("'"&S0dti&"'!"&Acol&1&Asep&Acol&4,FALSE))
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&1&Acol&1&Asep&Arow&1&Acol&3+Nloc,FALSE))                                                                                                      by: DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<"&Avers&">"&Atmp&"&R&""Arial,Reg
                                                                                                                                                                      = PAGE.SETUP("&L&""Arial,Regular""&8Built =FORMAT.MOVE(380,6,Arow&121&Acol&1) 35                      35
                                                              =FONT.PROPERTIES("Arial","Bold",12,,,,,,,1)
                                 =FONT.PROPERTIES("Arial","Bold",12,,,,,,,1)            = COLUMN.WIDTH(,,,3)                                                          = FORMULA.GOTO(Arow&1&Acol&1,TRUE) =SELECT("Button 3")                                   36           36
                                 =ALIGNMENT(7,FALSE,3,0,FALSE)                          = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&2&Acol&2,FALSE))
                                                              =ALIGNMENT(7,FALSE,3,0,FALSE)                                                                           =NEXT()                                       =FORMAT.SIZE(300,36)                       37           37
                                                              =FORMULA("Sample From",Arow&3&Acol&1)
                                 =FORMULA("Loci",Arow&3&Acol&4)                         = OPTIONS.VIEW(TRUE,TRUE)                                                                                                   =FREEZE.PANES(TRUE,0,1)                    38           38
                                                              =FORMULA("#Alleles",Arow&3&Acol&2)
                                                                                        = ECHO(TRUE)
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&3&Acol&4&Asep&Arow&3&Acol&3+Nloc,FALSE))                                                        ! Reset Macro page as well                    =FORMAT.MOVE(-150,-2,Arow&1&Acol&3) 39                  39
                                 =ALIGNMENT(7,FALSE,3,0,FALSE)                          = MESSAGE(TRUE,"Missing a valid day, month, year format.")
                                                              =FORMULA("#Loci",Arow&3&Acol&3)                                                                         =ACTIVATE(S0mac)                              =FORMULA.GOTO(Arow&1&Acol&1,TRUE) 40                    40
                                                                                        = ALERT("Please                                                               =PAGE.SETUP("&L&""Arial,Regular""&8Built by: Number > Date)."&Achr&Achr&"Then click 'Check It' to proceed.")
                                                              =FORMULA("Best Match To",Arow&3&Acol&5) format this as a valid date (day, month, year) with"&Achr&" Excel's standard cell format tool (Format > Cells… > DL Duewer, NIST&C&""Arial,Bold""&16"&Asyst&" &""Arial,Regular""&12<Version "&Avers&">"&Atmp&"&R&""A
                                 =FORMULA("Type",Arow&4&Acol&1)                                                                                                                                                                                                41           41
                                                              =FORMULA("%Allele",Arow&3&Acol&6)
                                 =FORMULA("Description",Arow&4&Acol&2)                  = HALT()                                                                                                                    ! Format the ControlPanel
                                                                                                                                                                      =SELECT(INDIRECT("'"&S0mac&"'!"&Arow&6&Acol&3&Asep&Arow&7&Acol&3,FALSE))                 42           42
                                                              =FORMULA("%Loci:1+",Arow&3&Acol&7)
                                                                                        =ELSE()
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&4&Acol&1&Asep&Arow&4&Acol&2,FALSE))                                                             =CLEAR(3)                                     =ACTIVATE(S0con)                           43           43
                                 =ALIGNMENT(3,FALSE,3,0,FALSE)                          = FORMULA("Valid",INDIRECT("'"&S0dti&"'!"&Arow&2&Acol&4,FALSE))
                                                              =FORMULA("Next Best Match To",Arow&3&Acol&9)                                                                                                                                                     44           44
                                                                                                                                                                                                                    =SELECT(INDIRECT("'"&S0con&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                 =BORDER(1)                                             = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&2&Acol&2,FALSE))
                                                              =FORMULA("%Allele",Arow&3&Acol&10)                                                                      =FORMULA.GOTO(Arow&1&Acol&25,TRUE) =ROW.HEIGHT(,,,3)                                     45           45
                                 =FOR("Iloc",1,Nloc,1)                                  = FORMAT.NUMBER(AlngDat)
                                                              =FORMULA("%Loci:1+",Arow&3&Acol&11)                                                                                                                   =SELECT(INDIRECT("'"&S0con&"'!"&Acol&1&Asep&Acol&2,FALSE))
                                                                                                                                                                      =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")                             46           46
                                                              =SELECT("("&Arow&3&Acol&1&Asep&Arow&3&Acol&3&Alsp&Arow&3&Acol&5&Asep&Arow&3&Acol&7&Alsp&Arow&3&Acol&9&Asep&Arow&3&Acol&11&")")
                                                                                        =END.IF()
                                 = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0dat&"'!"&Arow&4&Acol&3+Iloc,FALSE)))                                                          =HALT()                                       =COLUMN.WIDTH(,,,3)                        47           47
                                                              =ALIGNMENT(3)
                                 = FORMULA(Atmp,Arow&4&Acol&3+Iloc)                                                                                                                                                                                            48           48
                                 =NEXT()                      =BORDER(1)                ! Check the Time format                                                                                                     =SELECT("Button 19")                       49           49
                                                              =ECHO(TRUE)               =SET.NAME("AlngTim",DEREF(INDIRECT("'"&S0dti&"'!"&Arow&3&Acol&3,FALSE)))
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&4&Acol&4&Asep&Arow&4&Acol&3+Nloc,FALSE))                                                                                                      =FORMAT.SIZE(95,36)                        50           50
                                 =ALIGNMENT(3)                                          =SET.NAME("Itmp0",TEXT(NOW(),AlngTim))
                                                              =SELECT(INDIRECT("'"&S0rp2&"'!"&Acol&1&Asep&Acol&256,FALSE))                                                                                          =FORMAT.MOVE(2,2,Arow&Cbut0row&Acol&1)     51           51
                                 =BORDER(1)                   =COLUMN.WIDTH(0.5)        =IF(Itmp0=AlngTim,SET.NAME("Itmp0",NA()))                                                                                                                              52           52
                                                              =COLUMN.WIDTH(,,,3)       =IF(ISERROR(Itmp0*1),SET.NAME("Itmp0",NA()))                                                                                =SELECT("Button 1")                        53           53
                                 ! Make match list                                      =IF(ISERROR(Itmp0))
                                                              =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                                                                                     =FORMAT.SIZE(95,36)                        54           54
                                 =ACTIVATE(S0wrk)             =ECHO(FALSE)              = SET.NAME("NumNew",NumNew+1)                                                                                               =FORMAT.MOVE(2,2,Arow&Cbut1row&Acol&1)     55           55
                                                                                        = FORMULA("Invalid!",INDIRECT("'"&S0dti&"'!"&Arow&3&Acol&4,FALSE))
                                 =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&116&Asep&Acol&119,FALSE))                                                                                                                                                                56           56
                                 =CLEAR(3)                    =SET.NAME("Ilne",Nlne+1) = SELECT(INDIRECT("'"&S0dti&"'!"&Acol&1&Asep&Acol&4,FALSE))                                                                  =SELECT("Button 3")
                                                              =SET.NAME("Krow",0)       = COLUMN.WIDTH(,,,3)
                                 =COPY(INDIRECT("'"&S0rp2&"'!"&Acol&1,FALSE),INDIRECT("'"&S0wrk&"'!"&Acol&116,FALSE))                                                                                               =FORMAT.SIZE(95,36)
                                                              =FOR("Iums",1,Nums,1)     = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&3&Acol&2,FALSE))
                                 =COPY(INDIRECT("'"&S0rp2&"'!"&Acol&5,FALSE),INDIRECT("'"&S0wrk&"'!"&Acol&117,FALSE))                                                                                               =FORMAT.MOVE(2,2,Arow&Cbut2row&Acol&1)
                                                                                        = OPTIONS.VIEW(TRUE,TRUE)
                                 =SELECT(INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&116&Asep&Arow&3&Acol&117,FALSE))                                                                                                       =SELECT("Option Button 21")
                                 =CLEAR(3)                    ! Find where it is        = ECHO(TRUE)                                                                                                                =FORMAT.MOVE(1,-3,Arow&Cbut2row&Acol&2)
                                 =BORDER(0,0,0,0,0)                                     = MESSAGE(TRUE,"Missing a valid hours, minutes, seconds
                                                              = SET.NAME("Rout",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iums&Acol&132,FALSE))) format.")                                                                 =SELECT("Option Button 22")
                                                                                        = ALERT("Please                                                                                                              Cells… > Number > Time)."&Achr&Achr&"Then click
                                 =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&116&Asep&Acol&117,FALSE)) format this as a valid time (hours, minutes, seconds) with"&Achr&" Excel's standard cell format tool (Format >=FORMAT.MOVE(1,-3,Arow&Cbut2row+1&Acol&2) 'Check It' to proceed.")
                                                              = SET.NAME("Cout",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iums&Acol&133,FALSE)))
                                 =SORT(1,Arow&1&Acol&116,1,,,,,2)                       = HALT()                                                                                                                    =SELECT("Option Button 24")
                                                              ! Get the best match data =ELSE()                                                                                                                     =FORMAT.MOVE(1,-3,Arow&Cbut2row+2&Acol&2)
                                                              = SET.NAME("Asam",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout&Acol&Cout,FALSE)))
                                 ! If {A,B} and {B,A}, then get rid of {B,A}            = FORMULA("Valid",INDIRECT("'"&S0dti&"'!"&Arow&3&Acol&4,FALSE))                                                             =SELECT("Group Box 23")
                                 =FOR("Iums",1,Nums,1)                                  = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&3&Acol&2,FALSE))
                                                              = SET.NAME("Nall",1*MID(INDIRECT("'"&S0rp1&"'!"&Arow&Rout+1&Acol&Cout,FALSE),11,100))                                                                 =FORMAT.SIZE(180,48)
                                                              = SET.NAME("Nlps",1*MID(INDIRECT("'"&S0rp1&"'!"&Arow&Rout+2&Acol&Cout,FALSE),8,100))
                                                                                        = FORMAT.NUMBER(AlngTim)
                                 = SET.NAME("Asam1",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iums&Acol&116,FALSE)))                                                                                                       =FORMAT.MOVE(0,-6,Arow&Cbut2row&Acol&2)
                                                              = FORMULA(Asam,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&1,FALSE))
                                                                                        =END.IF()
                                 = SET.NAME("Asam2",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Iums&Acol&117,FALSE)))                                                                                                       =FORMULA(1,INDIRECT("'"&S0con&"'!"&Arow&Cbut2row&Acol&1,FALSE))
                                                              = FORMULA(Nall,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&2,FALSE))
                                 = SET.NAME("Irow",MATCH(Asam2,INDIRECT("'"&S0wrk&"'!"&Acol&116,FALSE)))
                                                                                        ! Check the DateTime format
                                 = IF(NOT(ISERROR(Irow))) = FORMULA(Nlps,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&3,FALSE))                                                                                         =SELECT("Button 4")
                                                                                        =SET.NAME("AlngDatTim",AlngDat&" "&AlngTim)
                                 = SET.NAME("Atmp",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Irow&Acol&117,FALSE)))                                                                                                        =FORMAT.SIZE(95,36)
                                 = IF(Atmp=Asam1)                                       =FORMULA(AlngDatTim,INDIRECT("'"&S0dti&"'!"&Arow&4&Acol&3,FALSE))
                                                              = SET.NAME("Asam",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout&Acol&Cout+1,FALSE)))                                                                        =FORMAT.MOVE(2,2,Arow&Cbut3row&Acol&1)



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                           11/18/2010 6:23 PM                                                                                                                                     29 / 38
Built by: DL Duewer, NIST
                                                                                                                      STR_MatchSamples <Version 2-Jun-09>                                                                                                                              Paid for, in part, by: NIJ

                                                                                                        A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                 =                             = SET.NAME("Pall",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout&Acol&Cout+2,FALSE)))
                                                                                        =SET.NAME("Itmp0",TEXT(NOW(),AlngDatTim))
                                        SELECT(INDIRECT("'"&S0wrk&"'!"&Arow&Irow&Acol&116&Asep&Arow&Irow&Acol&117,FALSE))
                                 =      CLEAR(3)                                        =IF(Itmp0=AlngDatTim,SET.NAME("Itmp0",NA()))
                                                               = SET.NAME("Ploc",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout&Acol&Cout+3,FALSE)))                                                                      =SELECT("Button 5")
                                 = END.IF()                                             =IF(ISERROR(Itmp0*1),SET.NAME("Itmp0",NA()))
                                                               = FORMULA(Asam,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&5,FALSE))                                                                                   =FORMAT.SIZE(95,36)
                                 = END.IF()                                             =IF(ISERROR(Itmp0))
                                                               = FORMULA(Pall,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&6,FALSE))                                                                                   =FORMAT.MOVE(2,2,Arow&Cbut4row&Acol&1)
                                 =NEXT()                                                = SET.NAME("NumNew",NumNew+1)
                                                               = FORMULA(Ploc,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&7,FALSE))                                                                                   =SELECT("Option Button 8")
                                                                                        = FORMULA("Invalid!",INDIRECT("'"&S0dti&"'!"&Arow&4&Acol&4,FALSE))                                                         =FORMAT.MOVE(1,-3,Arow&Cbut4row&Acol&2)
                                                                                        = SELECT(INDIRECT("'"&S0dti&"'!"&Acol&1&Asep&Acol&2,FALSE))
                                 ! Sort the interesting pairs = SET.NAME("Asam",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout+1&Acol&Cout+1,FALSE)))                                                                     =SELECT("Option Button 9")
                                                               = SET.NAME("Pall",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout+1&Acol&Cout+2,FALSE)))
                                                                                        = COLUMN.WIDTH(,,,3)
                                 =SELECT(INDIRECT("'"&S0wrk&"'!"&Acol&116&Asep&Acol&117,FALSE))                                                                                                                    =FORMAT.MOVE(1,-3,Arow&Cbut4row+1&Acol&2)
                                                               = SET.NAME("Ploc",DEREF(INDIRECT("'"&S0rp1&"'!"&Arow&Rout+1&Acol&Cout+3,FALSE)))
                                 =SORT(1,Arow&1&Acol&116,1,,,,,2)                       = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&4&Acol&1,FALSE))                                                                     =SELECT("Group Box 20")
                                                               = FORMULA(Asam,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&9,FALSE))
                                                                                        = OPTIONS.VIEW(TRUE,TRUE)
                                 =SET.NAME("Npai",COUNTA(INDIRECT("'"&S0wrk&"'!"&Acol&116,FALSE)))                                                                                                                 =FORMAT.SIZE(180,36)
                                                                                        = ECHO(TRUE)
                                                               = FORMULA(Pall,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&10,FALSE))                                                                                  =FORMAT.MOVE(0,-6,Arow&Cbut4row&Acol&2)
                                 ! Show page                                            = MESSAGE(TRUE,"Date-Time format is not valid.")
                                                               = FORMULA(Ploc,INDIRECT("'"&S0rp2&"'!"&Arow&Iums+3&Acol&11,FALSE))                                                                                  =FORMULA(2,INDIRECT("'"&S0con&"'!"&Arow&Cbut4row&Acol&1,FALSE))
                                 =ACTIVATE(S0rp3)              =NEXT()                  = ALERT("While the Date and Time formats are ok, their combination is NOT ok."&Achr&"Try reformatting the Date and/or the Time so that the combination of the two is valid using "&Achr&"Excel's Format > Cells… > Number > Custom tool.
                                                                                        = HALT()
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Acol&1&Asep&Acol&256,FALSE))                                                                                                                      =SELECT("Check Box 28")
                                 =COLUMN.WIDTH(0.5)            ! Sort                   =ELSE()                                                                                                                    =FORMAT.MOVE(1,-1,Arow&Ckbx1row&Acol&2)
                                 =COLUMN.WIDTH(,,,3)                                    = FORMULA("Valid",INDIRECT("'"&S0dti&"'!"&Arow&4&Acol&4,FALSE))
                                                               =SELECT(INDIRECT("'"&S0rp2&"'!"&Arow&4&Acol&1&Asep&Arow&Nums+3&Acol&11,FALSE))                                                                      =FORMAT.SIZE(14,14)
                                 =ECHO(TRUE)                                            = SELECT(INDIRECT("'"&S0dti&"'!"&Arow&4&Acol&2,FALSE))
                                                               =SORT(1,Arow&4&Acol&7,2,Arow&4&Acol&6,2,,,2)                                                                                                        =CHECKBOX.PROPERTIES(0,Arow&Ckbx1row&Acol&2)
                                 =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                      = FORMAT.NUMBER(AlngDatTim)                                                                                                =SELECT("Check Box 29")
                                 =ECHO(FALSE)                  ! Format                 =END.IF()                                                                                                                  =FORMAT.MOVE(1,-1,Arow&Ckbx2row&Acol&2)
                                                               =SELECT(INDIRECT("'"&S0rp2&"'!"&Acol&1&Asep&Acol&256,FALSE))                                                                                        =FORMAT.SIZE(14,14)
                                 ! Loop over each Unmatched    =COLUMN.WIDTH(0.5)       ! Success                                                                                                                  =CHECKBOX.PROPERTIES(0,Arow&Ckbx2row&Acol&2)
                                 =SET.NAME("Krow",2)           =COLUMN.WIDTH(,,,3)      =ACTIVATE(S0dti)                                                                                                           =SELECT("Check Box 35")
                                 =FOR("Ipai",1,Npai,1)                                  =FORMULA("AlngDat",INDIRECT("'"&S0mac&"'!"&Arow&17&Acol&2,FALSE))
                                                               =FORMULA.GOTO(Arow&1&Acol&1,TRUE)                                                                                                                   =FORMAT.MOVE(1,-1,Arow&Ckbx3row&Acol&2)
                                                                                        =FORMULA("AlngTim",INDIRECT("'"&S0mac&"'!"&Arow&18&Acol&2,FALSE))
                                 = SET.NAME("Asam1",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Ipai&Acol&116,FALSE)))                                                                                                      =FORMAT.SIZE(14,14)
                                                               =ACTIVATE(S0con)         =FORMULA("AlngDatTim",INDIRECT("'"&S0mac&"'!"&Arow&19&Acol&2,FALSE))
                                 = SET.NAME("Asam2",DEREF(INDIRECT("'"&S0wrk&"'!"&Arow&Ipai&Acol&117,FALSE)))                                                                                                      =CHECKBOX.PROPERTIES(0,Arow&Ckbx3row&Acol&2)
                                                               =ECHO(TRUE)              =FORMULA("Acol",INDIRECT("'"&S0mac&"'!"&Arow&20&Acol&2,FALSE))
                                 = SET.NAME("Irow",MATCH(Asam1,INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE),0))                                                                                                           =SELECT("Check Box 36")
                                 = IF(ISERROR(Irow))                                    =FORMULA("Adcp",INDIRECT("'"&S0mac&"'!"&Arow&21&Acol&2,FALSE))
                                                               =FORMULA("Finished",INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched1col,FALSE))                                                                            =FORMAT.MOVE(1,-1,Arow&Ckbx4row&Acol&2)
                                                               =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&5&Acol&Ched1col+1,FALSE))
                                                                                        =FORMULA("Agnl",INDIRECT("'"&S0mac&"'!"&Arow&22&Acol&2,FALSE))
                                 = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Unexpected error with Unmatched Sample # "&Ipai&" ("&Asam1&").")                                                                =FORMAT.SIZE(14,14)
                                 = ECHO(TRUE)                                           =FORMULA("Alsp",INDIRECT("'"&S0mac&"'!"&Arow&23&Acol&2,FALSE))                                                             =CHECKBOX.PROPERTIES(0,Arow&Ckbx4row&Acol&2)
                                                               =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                                                                         in "&S0dat&" worksheet."&Achr&Achr&"Please contact david.duewer@NIST.gov about this hassle.",2)
                                 = ALERT("Unexpected error... could not find "&Asam1&" =FORMULA("Arow",INDIRECT("'"&S0mac&"'!"&Arow&24&Acol&2,FALSE))                                                              =SELECT("Check Box 37")
                                 = HALT()                      =RETURN()                =FORMULA("NumNew",INDIRECT("'"&S0mac&"'!"&Arow&25&Acol&2,FALSE))                                                           =FORMAT.MOVE(1,-1,Arow&Ckbx5row&Acol&2)
                                 = END.IF()                                             =FORMULA(AlngDat,INDIRECT("'"&S0mac&"'!"&Arow&17&Acol&3,FALSE))                                                            =FORMAT.SIZE(14,14)
                                                                                        =FORMULA(AlngTim,INDIRECT("'"&S0mac&"'!"&Arow&18&Acol&3,FALSE))
                                 = SET.NAME("Jrow",MATCH(Asam2,INDIRECT("'"&S0dat&"'!"&Acol&2,FALSE),0))                                                                                                           =CHECKBOX.PROPERTIES(0,Arow&Ckbx5row&Acol&2)
                                 = IF(ISERROR(Jrow))                                    =FORMULA(AlngDatTim,INDIRECT("'"&S0mac&"'!"&Arow&19&Acol&3,FALSE))
                                                                                        =FORMULA(Acol,INDIRECT("'"&S0mac&"'!"&Arow&20&Acol&3,FALSE))
                                 = MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Unexpected error with Unmatched Sample # "&Ipai&" ("&Asam2&").")                                                                =SELECT("Button 30")
                                 = ECHO(TRUE)                                           =FORMULA(Adcp,INDIRECT("'"&S0mac&"'!"&Arow&21&Acol&3,FALSE))                                                               =FORMAT.SIZE(95,24)
                                                                                         in "&S0dat&" worksheet."&Achr&Achr&"Please contact david.duewer@NIST.gov about this hassle.",2)
                                 = ALERT("Unexpected error... could not find "&Asam2&" =FORMULA(Agnl,INDIRECT("'"&S0mac&"'!"&Arow&22&Acol&3,FALSE))                                                                =FORMAT.MOVE(2,2,Arow&Cbut5row&Acol&1)
                                 = END.IF()                                             =FORMULA(Alsp,INDIRECT("'"&S0mac&"'!"&Arow&23&Acol&3,FALSE))                                                               =SELECT("Button 31")
                                                                                        =FORMULA(Arow,INDIRECT("'"&S0mac&"'!"&Arow&24&Acol&3,FALSE))                                                               =FORMAT.SIZE(95,24)
                                 ! Write out these profiles                             =FORMULA(NumNew,INDIRECT("'"&S0mac&"'!"&Arow&25&Acol&3,FALSE))                                                             =FORMAT.MOVE(2,2,Arow&Cbut6row&Acol&1)
                                 = SET.NAME("Krow",Krow+3)                                                                                                                                                         =SELECT("Button 32")
                                                                                        ! Hide this worksheet
                                 = COPY(INDIRECT("'"&S0dat&"'!"&Arow&Irow&Acol&1&Asep&Arow&Irow&Acol&3+Nloc,FALSE),INDIRECT("'"&S0rp3&"'!"&Arow&Krow&Acol&1,FALSE))                                                =FORMAT.SIZE(95,24)
                                                                                        =ACTIVATE(S0dti)
                                 = COPY(INDIRECT("'"&S0dat&"'!"&Arow&Jrow&Acol&1&Asep&Arow&Jrow&Acol&3+Nloc,FALSE),INDIRECT("'"&S0rp3&"'!"&Arow&Krow+1&Acol&1,FALSE))                                              =FORMAT.MOVE(2,2,Arow&Cbut7row&Acol&1)
                                                                                        =WORKBOOK.HIDE()
                                 = SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&Krow&Acol&1&Asep&Arow&Krow+1&Acol&2+Iloc,FALSE))
                                 = PATTERNS(0)                                                                                                                                                                     =SELECT("Button 10")
                                                                                        ! Do we need to restart Auto_Open?
                                 = SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&Krow&Acol&4&Asep&Arow&Krow+1&Acol&2+Iloc,FALSE))                                                                                            =FORMAT.SIZE(95,36)
                                 = IF(ClrCde1<0.5,PATTERNS(0),PATTERNS(1,,ClrCde1,TRUE))=IF(NumNew>0.5)                                                                                                            =FORMAT.MOVE(2,2,Arow&Cbut8row&Acol&1)
                                                                                        = Auto_Open()
                                 ! Color code the locus profiles for this pair          =ELSE()                                                                                                                    =SELECT("Button 25")
                                 = FOR("Iloc",1,Nloc,1)                                 = ACTIVATE(S0con)                                                                                                          =FORMAT.SIZE(95,24)
                                                                                        = FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                 = SET.NAME("Asam1",DEREF(INDIRECT("'"&S0rp3&"'!"&Arow&Krow&Acol&3+Iloc,FALSE)))                                                                                                   =FORMAT.MOVE(2,2,Arow&71&Acol&1)
                                                                                        =END.IF()
                                 = SET.NAME("Asam2",DEREF(INDIRECT("'"&S0rp3&"'!"&Arow&Krow+1&Acol&3+Iloc,FALSE)))                                                                                                 =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                 = SELECT(INDIRECT("'"&S0rp3&"'!"&Arow&Krow&Acol&3+Iloc&Asep&Arow&Krow+1&Acol&3+Iloc,FALSE))
                                                                                        =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" Welcome to "&Asyst&"!")                                                             =RUN(SMS_CheckCutoffs,FALSE)
                                 ! If either is "?", flag COOL                          =RETURN()
                                 = IF(OR(Asam1="?",Asam2="?"))                                                                                                                                                     ! Reset the User info
                                 = IF(ClrCde2<0.5,PATTERNS(0),PATTERNS(1,,ClrCde2,TRUE))                                                                                                                           =ACTIVATE(S0mac)
                                                                                                                                                                                                                   =ERROR(FALSE)
                                 ! See if there is at least one common allele                                                                                                                                      = WORKBOOK.UNHIDE("MS_Macro")
                                 = ELSE.IF(NOT(Asam1=Asam2))                                                                                                                                                       =ERROR(TRUE)
                                 =      SET.NAME("Ntmp",MAX(2,COUNTA(INDIRECT("'"&S0wrk&"'!"&Acol&120,FALSE))))                                                                                                    =FORMULA("NewUser",INDIRECT("'"&S0mac&"'!"&Arow&6&Acol&3,FALSE))
                                 =      FORMULA.ARRAY("",INDIRECT("'"&S0wrk&"'!"&Arow&1&Acol&120&Asep&Arow&Ntmp&Acol&120,FALSE))                                                                                   =FORMULA(0,INDIRECT("'"&S0mac&"'!"&Arow&"7C3",FALSE))
                                 =      SET.NAME("Iall",0)                                                                                                                                                         =FORMULA.GOTO(Arow&1&Acol&1,TRUE)
                                 =      SET.NAME("Istr",1)                                                                                                                                                         =WORKBOOK.HIDE()
                                 =      SET.NAME("Atmp",Asam1&Alsp)
                                 =      SET.NAME("Ntmp",LEN(Asam1))                                                                                                                                                =ACTIVATE(S0con)
                                 =      FOR("Itmp",1,Ntmp,1)                                                                                                                                                       =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")
                                 =       SET.NAME("Itmp",FIND(Alsp,Atmp,Istr))                                                                                                                                     =HALT()
                                 =       SET.NAME("Atmp1",MID(Atmp,Istr,Itmp-Istr))
                                 =       SET.NAME("Istr",Itmp+1)
                                 =       IF(NOT(Atmp1="?"))
                                 =        SET.NAME("Iall",Iall+1)
                                 =        FORMULA("'"&Atmp1,INDIRECT("'"&S0wrk&"'!"&Arow&Iall&Acol&120,FALSE))



eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                             11/18/2010 6:23 PM                                                                                                                                         30 / 38
Built by: DL Duewer, NIST
                                                                                                                STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                                                                  A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                 =     END.IF()
                                 =     NEXT()

                                 ! Count the number of matches in the second
                                 = SET.NAME("Iall",0)
                                 = SET.NAME("Istr",1)
                                 = SET.NAME("Atmp",Asam2&Alsp)
                                 = SET.NAME("Ntmp",LEN(Atmp))
                                 =    FOR("Itmp",1,Ntmp,1)
                                 =     SET.NAME("Itmp",FIND(Alsp,Atmp,Istr))
                                 =     SET.NAME("Atmp1",MID(Atmp,Istr,Itmp-Istr))
                                 =     SET.NAME("Istr",Itmp+1)
                                 =     IF(NOT(ISERROR(MATCH(Atmp1,INDIRECT("'"&S0wrk&"'!"&Acol&120,FALSE),0))),SET.NAME("Iall",Iall+1))
                                 =    NEXT()

                                 ! Al least one allele in common, flag WARM else HOT
                                 =     IF(Iall>0.5,IF(ClrCde3<0.5,PATTERNS(0),PATTERNS(1,,ClrCde3,TRUE)),IF(ClrCde4<0.5,PATTERNS(0),PATTERNS(1,,ClrCde4,TRUE)))
                                 = END.IF()
                                 = NEXT()
                                 =NEXT()

                                 ! Format
                                 =ECHO(TRUE)
                                 =SELECT(INDIRECT("'"&S0rp3&"'!"&Acol&1&Asep&Acol&256,FALSE))
                                 =COLUMN.WIDTH(0.5)
                                 =COLUMN.WIDTH(,,,3)
                                 =FORMULA.GOTO(Arow&1&Acol&1,TRUE)

                                 =ACTIVATE(S0con)
                                 =ECHO(TRUE)
                                 =FORMULA("Finished",INDIRECT("'"&S0con&"'!"&Arow&6&Acol&Ched1col,FALSE))
                                 =FORMULA(NOW(),INDIRECT("'"&S0con&"'!"&Arow&6&Acol&Ched1col+1,FALSE))

                                 =MESSAGE(TRUE,TEXT(NOW(),AlngDatTim)&" "&R1C&" > Finished!")




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                                                                    11/18/2010 6:23 PM                                           31 / 38
Built by: DL Duewer, NIST
                                                                  STR_MatchSamples <Version 2-Jun-09>                             Paid for, in part, by: NIJ

                                                    A System for Matching Samples Across Excel Worksheets by Their STR Profiles



                                 =RETURN()




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!MS_Macro                                  11/18/2010 6:23 PM                                               32 / 38
   Built by: David lee Duewer                                                                                                                                             Paid for by: NIST
                                                                  PDF_MakerTotal       <Version 01-May-07>
                                                       An Exploratory Consensus Assessment Tool for Interlaboratory Data
Parameter           Value                Format          Status
     Date         2-Jun-09             d-mmm-yy          Valid
                                                                                                              Date-Time format issue!
     Time         10:11:54             hh:mm:ss          Valid    Different versions of Excel support different date and time formats.
 DateTime     2-Jun-09 10:11:54    d-mmm-yy hh:mm:ss     Valid    The default formats (in Excel-eze, "dd-mm-yy" and "hh:mm:ss") are not valid for your system.

                                                                  Please use Excel's standard Format menubar tool to format the contents of cells B2 and B3 (rows 2 and 3 of col 2).
                                                                  to show the date (day, month and year) and time (hour, minute, second).
                                                                  In the Excel system used to build this program, the date and time format tool is accessible from the
                                                                  "Number" page of the "Cells…" entry of the "Format" menu of the MenuBar (Format > Cells… > Number).

                                                                  Use the "Date" item of the Number page to set the date format of cell B2 (row 2 col 2) and
                                                                  the "Time" item to set the time format of cell B3 (row 3 col 2).
                                                                  Once the contents of these two cells display to you satisfaction, click the Check It button below.

                                                                  With any luck, you should never see this page (in this particular program) again!
                                                                  If you encounter difficulties, please contact Dave Duewer (David.Duewer@NIST.gov) with details.


                                                                                  Check It




   eaf62aea-c045-4b94-902b-603d800b7a8c.xls!DateTime                                  11/18/2010 6:23 PM                                                                            33 / 38
Built by: David lee Duewer                                                                                              Paid for by: NIST
                                                              PDF_MakerTotal        <Version 01-May-07>
                                                    An Exploratory Consensus Assessment Tool for Interlaboratory Data




                                                                                                                                        140
                                                                                                                                        0.7




eaf62aea-c045-4b94-902b-603d800b7a8c.xls!DateTime                            11/18/2010 6:23 PM                                   34 / 38
 .
 ,
 ;
?
0
1
2
3
4
5
6
7
8
9
O
L
X
Y

				
DOCUMENT INFO
Description: Matching Chemical Change Worksheet document sample