Data Processor

The Data Processor

The Data Processor is the most important part of each VSD. It takes raw match data, organizes it, and stores it in an easily-viewable format for analysis. Without it, the VSD would practically be useless.

The data processor found in VSD19 and VSD20, DP19, is made up of two components: the formula block and the processor macro. The data processor in VSD18, on the other hand, consists solely of a series of formula blocks.

The Processor Formula

The table cells in the processor sheet of each VSD contain a variation of this formula, one you may or may not have seen before:

{=IFERROR(IF(INDEX(RawData,SMALL(IF(RawTeams=$A$1,ROW(RawTeams)),COLUMN(A:A))-$X$1,ROW(2:2))="","0",INDEX(RawData,SMALL(IF(RawTeams=$A$2,ROW(RawTeams)),COLUMN(A:A))-$X$1,ROW(2:2))),"--")}

Now, this may look like a heap of gibberish nonsense, but trust me, everything in it serves a purpose. Let's break it down piece-by-piece to see how it works:

Notice that the formula is wrapped in curly brackets or braces {}. This designates it as an array formula. An array formula, like the name implies, accepts an array of values as inputs and/or returns an array of values as its output.
To designate a formula as an array formula, hit Ctrl+Shift+Enter (or your operating system's equivalent) once you've entered it. This will automatically add braces {} around your formula.
Please note the braces denoting an array formula can not be added manually, and that they will be removed if you edit the formula again.

The Core Formula

Now, let's look at the contents of the formula itself. Notice that this formula is just another smaller formula wrapped in an IFERROR function. We can ignore the IFERROR function for now, leaving us with the following formula:

{=IF(INDEX(RawData,SMALL(IF(RawTeams=$A$2,ROW(RawTeams)),COLUMN(A:A))-$X$1,ROW(2:2))="","0",INDEX(RawData,SMALL(IF(RawTeams=$A$2,ROW(RawTeams)),COLUMN(A:A))-$X$1,ROW(2:2)))}

Now, notice that this smaller formula is made up of two copies of a smaller inner formula wrapped in an IF function. We can ignore the IF function for the time being, and instead focus on one copy of the inner formula. This leaves us with the core of the processor formula, the formula which does most of the work in the VSD:

{=INDEX(RawData,SMALL(IF(RawTeams=$A$2,ROW(RawTeams)),COLUMN(A:A))-$X$1,ROW(2:2))}

Breaking this core formula down gives us the following:

{=INDEX(                                                                            )}
        RawData, SMALL(                                             )-$X$1, ROW(2:2)
                       IF(RawTeams=$A$2, ROW(RawTeams)), COLUMN(A:A)

To fully make sense of this formula, I'll need to give context for some of the terms in it:

• RawData = INPUT!$A$3:$AC$10370: A range containing all the data in the INPUT sheet
• RawTeams = INPUT!$A$3:$A$10002: A range containing all the team numbers entered into the INPUT sheet
• $A$2 = DP19!$A$2: A cell containing the number of the team whose data is being processed
• $X$1 = DP19!$X$1: A cell containing a constant which aligns data to the correct cells
• ROW() and COLUMN(): Dynamic values which change based on the row/column an instance of the processor formula is located in

Now that those terms have been put into context, we can take a look at how this formula actually works!

• INDEX(range, row, column) returns a value at a specific row and column within a range. In this case, it returns a value in RawData (the range) at a dynamically-determined column (ROW()) and a row determined by...
  • SMALL(array, k), which returns the k-th smallest value in an array. Here, it is being fed...
    • IF(RawTeams=$A$2, ROW(RawTeams)), an if statement which checks if the team number of the team that is being processed ($A$2) matches a team number in RawTeams. Since the processor formula is an array formula, the entire RawTeams array is fed into this statement, which checks every team number entry in the array to see if it matches the team number in cell A2. If an entry is a match, that entry's row is returned (ROW(RawTeams)).
  • Then, of the matches from the if statement, the k-th smallest (with k being determined by the dynamic value COLUMN()) is returned. Since all the matches will have the same team number ($A$2), this effectively returns the k-th instance of that number in RawTeams. If there are no matches, the SMALL function will throw an error.
• As long as no error is thrown, the output from the SMALL function will be subtracted by the shift constant ($X$1), and the resulting output will be the row fed into the INDEX function, which will return one piece of data from the INPUT sheet.

And that's the core processor formula! As complex as it may look, it's not too tough to see what it does once you break it down.

The Formula Wrapper

Now, remember those IF and IFERROR functions we ignored before? Let's step back and take a look at them in the full processor formula:
(For simplicity's sake, I'll substitute the core formula with <<CORE>>)

{=IFERROR(                              ), "--")}
          IF(<<CORE>>)="", "0", <<CORE>>

These two functions form the wrapper for the core formula. With the core formula out of the way, it's much easier to see what they do!

• IFERROR(formula, return) checks if any errors have been thrown by the formula passed into it, and returns the value specified in return if so. In this case, it's checking if SMALL (from the core formula) has thrown an error of if any other unexpected behaviour has taken place inside the core formula, and returning a "--" if so.
• IF(<<CORE>>)="" checks if there was no data inputted into a certain cell (or, in other words, the core formula returned a blank cell). If so, it changes the value returned by the core formula to a "0" to avoid any potentially ambiguous behaviour when it is handled by the analysis formulas in other sheets.

The wrapper formed by these functions completes the processor formula, allowing it to process data much more smoothly.

The Formula Block

The Data Processor is made up of multiple instances of the processor formula above, creating what I will call a formula block.
When processing data, the entire formula block is activated, running the processor formula multiple times with different dynamic values (as determined by each formula's position within the formula block). Each instance of the processor formula returns a different data point, contributing to the formula block's output of an entire team's processed data. This output is then fed into the Storage sheet by the data processing macro.

The Processor Macro (VSD19/VSD20)

The Processor macro is a simple macro which iterates through each team in the team list, processing and copying data to Storage for each team at the event.
To better understand how it works, I'll be breaking down the VSD20's processor macro section-by-section.
(The VSD19's processor macro works very similarly; most of what I'm explaining here applies to that spreadsheet's processor macro as well)

The Configuration Section

The first part of the processor macro handles pre-processing configuration to make sure everything runs smoothly.

Obviously, before we can do anything to the spreadsheet, we need to declare which sheets we'll be accessing and modifying, as well as some other variables that will come in handy a little bit later on. In the case of the processor macro, those sheets are: Teams (wt), DP19 (wp), Storage (ws), and GUIDE (wg).
The active spreadsheet at the time of macro execution, active, will also come in handy later on in the macro.

' Declarations...
Dim row As Integer, col As Integer, fact As Integer, ctr As Integer, check As Integer

' ...and some more declarations too!
Dim wt As Worksheet, wp As Worksheet, ws As Worksheet, wg As Worksheet, active As Worksheet
Set wt = Worksheets("Teams")
Set wp = Worksheets("DP19")
Set ws = Worksheets("Storage")
Set wg = Worksheets("GUIDE")
Set active = ActiveSheet

After these declarations, a section of MDM-related code triggers, which I'll be ignoring for now since it doesn't affect the actual processor macro. If you'd like an explanation of what this section of code does, it'll be at the bottom of this wiki page under "Other Stuff/MDM caller".

Once everything above is handled, the processor macro copies the team list from Teams into DP19. This allows the processor to use the team list however it likes without actually modifying any information in Teams.
Notice how the processor macro activates each sheet before performing any operations on it. This ensures that sheets being accessed and/or modified are in focus when they're needed to help avoid errors or other unexpected behaviour while processing data. It's generally a good idea to activate a sheet whenever you're using it a lot.

' Copies team numbers from team list
wt.Activate
wt.Range("B3:B502").Select
Selection.Copy
wp.Activate
wp.Range("Y2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Once the team list has been copied over to DP19, a "stop value" is procedurally placed at the end of it. It's an arbitrary non-numerical value that ends the processor macro's processing loop, preventing the macro from running indefinitely and crashing.
The row and col variables (which were declared earlier) point to the first cell of the copied team list, which is where the stop value is being placed. The check variable acts as a backup escape mechanism for the stop value placement loop, and the fact (factor) variable is assigned a value for later.

' Variable config
row = wp.Range("X3").Value
col = wp.Range("X4").Value
fact = wt.Range("INDEX").Value
check = 0

' Places a 'stop value' at the end of the copied team list (19gen)
Do Until check = 1
    wp.Cells(row, col).Select
    If Selection.Value = "" Then
        Selection.Value = "{[{0x7effaf}]}"
        check = 1
    Exit Do
    Else
        row = row + 1
    End If
Loop

The stop value loop works by iterating through the column in DP19 with the copied team list and looking for the first empty cell in it. Once it finds an empty cell, it fills it with the stop value and ends the loop.
Now that all of the pre-processing configuration is done, the next section of the macro will ececute: the processing section!

The Processing Section

First, before processing anything, the row and check variables are reset to their values pre-stop value loop, and the ctr (counter) variable is set to 0. This ensures that the team numbers actually get fed through the processor macro!

' Variable reconfig
row = wp.Range("X3").Value
ctr = 0
check = 0

Once the variables are reset, the processor loop is executed:

' For each team... process data:
Do Until check = 1
    wp.Activate
    wp.Cells(row, col).Select
    
    ' Checks if the selected cell is the 'stop value'
    If Selection.Value = "{[{0x7effaf}]}" Then
        ' If so, ends the Do loop
        Let check = 1
        Selection.Clear
    Exit Do
    Else
        ' If not, copies the selected cell into "DP19" to begin data processing
        Selection.Copy
        wp.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        ' Copies data from "DP19" to "Storage"
        wp.Range("ProcessorCore").Copy
        ws.Activate
        ws.Cells(wt.Range("InitialIndex").Value + 1 + fact * ctr, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        ' Increments the variables
        Let row = row + 1
        Let ctr = ctr + 1
    End If
Loop

This loop might seems like it has a lot going on inside, but it's simpler than you might think. So... let's break it down!

The loop works as follows:

• Select the cell at (row, col) in DP19

' For each team... process data:
Do Until check = 1
    wp.Activate
    wp.Cells(row, col).Select

• Check the value of the selected cell
  • If the selected cell is the stop value, the processor macro ends the loop

' Checks if the selected cell is the 'stop value'
    If Selection.Value = "{[{0x7effaf}]}" Then
        ' If so, ends the Do loop
        Let check = 1
        Selection.Clear
    Exit Do

Otherwise, the processor macro begins processing data:

• Copy the currently selected team number in cell (row, col) to cell A2 (the processing key cell, which indicates the team whose data is being processed)

    Else
        ' If not, copies the selected cell into "DP19" to begin data processing
        Selection.Copy
        wp.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

• Wait for the data in DP19's formula block to be calculated (this is done automatically, yay!) then select and copy all the newly-calculated data

        ' Copies data from "DP19" to "Storage"
        wp.Range("ProcessorCore").Copy

• Activate Storage and paste the copied data into the cell determined by the formula (Teams!InitialIndex + 1 + fact * ctr, 2), which corresponds to the team's designated area in Storage
  • Teams!InitialIndex = the index of the first team in the team list in Teams (acts as a starting index)
  • fact * ctr = the difference between adjacent indices multiplied by the team's position in the copied team list (basically, a team index calculator)

        ws.Activate
        ws.Cells(wt.Range("InitialIndex").Value + 1 + fact * ctr, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

• Increment the row and ctr variables by 1, then repeat all the steps above until the stop value is reached

        ' Increments the variables
        Let row = row + 1
        Let ctr = ctr + 1
    End If
Loop

The Cleanup Section

Now that all the processing is done, it's time to clean everything up!
First, the processor macro resets DP19 to its initial state by clearing the team number from cell A2 and deleting the copied team list.

' Spring cleaning
wp.Activate
wp.Range("A2").Value = "X"
wp.Range("Y2:Y501").Clear

Finally, the processor macro resets the camera in all sheets used, to help keep the VSD nice and tidy. Since some sheets have frozen rows, selecting cell C3 then cell A1 helps ensure that the camera cleanup algorithm works properly.

' View cleanup
wp.Activate
wp.Range("C3").Select
wp.Range("A1").Select
ws.Activate
ws.Range("C3").Select
ws.Range("A1").Select
wt.Activate
wt.Range("C3").Select
wt.Range("A1").Select
wg.Activate
wg.Range("C3").Select
wg.Range("A1").Select
active.Activate

Once camera cleanup is done, the processor macro reactivates the sheet it was triggered from (active) and stops.

Tada! All of the VSD's data has been processed and stored in Storage!

Other Stuff

Feeds (VSD20)

More information about feeds will be added soon.
For now, all that needs to be said about them is that they're used to convert some raw data (such as CARD information) into a format the VSD can better understand. If you see rows in the DP19 sheet marked with FEED or CALC, those are feeds in action!

MDM caller (VSD20)

The MDM caller is the section of code in the processor I skipped over in the processor macro explanation above. It handles the processor-MDM integration when the "Auto match merge on data process" box is checked, but isn't a crucial part of the core processor itself.

ws.Activate
If ws.Range("AutoPullCheck") = True Then
    ' Awaits confirmation from the user
    wg.Activate
    wg.Range("FullProcessCheck").Value = 0
    FullProcessWarning.Show

    ' Checks if the user approved the action
    If wg.Range("FullProcessCheck").Value = 2 Then
        ' Approved; continue
        wg.Range("FullProcessCheck").Value = 0
        wg.Range("MDMCheck").Value = "[{{0x7effaf}}]"
        Call GetMatches
        Call MDM
    ElseIf wg.Range("FullProcessCheck").Value = 1 Then
        ' Partially approved; skip TBA extras
        wg.Range("FullProcessCheck").Value = 0
    Else
        ' Not approved; cancel
        Exit Sub
    End If
End If

Basically, this triggers a pop-up dialog box on macro activation if the "Auto match merge on data process" checkbox is checked, ensuring that the user who activated the macro actually wants to activate the MDM as well.
Due to the way dialog boxes work, the code will pause once it reaches FullProcessWarning.Show (the function which triggers the pop-up dialog box) and wait until the dialog box has been resolved.

ws.Activate
If ws.Range("AutoPullCheck") = True Then
    ' Awaits confirmation from the user
    wg.Activate
    wg.Range("FullProcessCheck").Value = 0
    FullProcessWarning.Show

Once the dialog box has been resolved, one of three things will happen depending on the button that was clicked:

• Run All: the processor macro will call the MDM, then process data once the MDM has finished

    If wg.Range("FullProcessCheck").Value = 2 Then
        ' Approved; continue
        wg.Range("FullProcessCheck").Value = 0
        wg.Range("MDMCheck").Value = "[{{0x7effaf}}]"
        Call GetMatches
        Call MDM

• Process Only: the processor macro will process data normally (ignoring the MDM)

    ElseIf wg.Range("FullProcessCheck").Value = 1 Then
        ' Partially approved; skip TBA extras
        wg.Range("FullProcessCheck").Value = 0

• Cancel: the processor macro will stop

    Else
        ' Not approved; cancel
        Exit Sub
    End If
End If

Conclusion

So that's the breakdown of the VSD's processor! I hope you learned something about what it does and how it works, and now have some new knowledge about formulas and macros that you can apply to other spreadsheets!