Excel Is Our Friend – Part III
As I child I remember my Dad doing most of the auto repair himself. And if anyone was outside doing anything, I was there (a simpler time, before Xbox, even before cable TV). So, when I use the word toolbox, I remember asking Dad… What is this one called? And What is this one for? And I remember him asking, hand me the so and so… my standard clueless response was “this one?”
While escaping the island has the appearance of being our main objective, the true focus is on learning the tools.
- How to: VLOOKUP – built-in function
This built-in Excel function is something you definitely want in your “toolbox”.
The VLOOKUP function, which stands for vertical lookup, can help you find specific information in large data tables. In addition, and equally useful, it can determine what is not in the table. In the example below, I use VLOOKUP to gather external information into a list formatted to my preferences. After creating so many PI Interfaces here, I was having trouble remembering the specifics without having to look for them. So, I built a spreadsheet for a quick reference. But I did not want to manually populate this listing. So with a few items from the toolbox, I was able to semi-automate the gathering of the data, as you will see below:
First, I imported the Module Database from my PI Server, into a separate sheet in my workbook (renamed that sheet “R2-Int1MDB”). I used the PI-SMT > Module Database Builder Add-in > Import Items. I just imported the tree where the interfaces reside (“\\R2-PI\%OSI\Interfaces”), including all subitems, values, etc., everything.
After determining which values were of use, I created column headings for my list, some of my columns are visible in the example below.
The next step was to get a listing of the interfaces, for column A. To get this, I went to the sheet “R2-Int1MDB”. After selecting any cell in the column headings of the “list”, I clicked Excel’s Data Menu, and clicked the “Filter” button. (Excel has a help topic on “Quick start: Filter data by using an AutoFilter”, You can point at the icon in the ribbon and press F1).
Using “AutoFilter”, for my “Type” column, I selected “Module”, for the “Name” filter, I used the Text Filter > “contains”, and entered a filter value of “\\*\*\*\*\*” (without the quotes). This filtered my list to machine\interface instance. Placing my activecell on the first item in the filtered list (clicking on that cell), I used the shortcuts mentioned above (Ctrl+Shift+DownArrow) to select the entire list. Then, Ctrl+G, followed by Alt+S (or click “Special”), Alt+Y (or click the “Visible cells only” radio button, then . Next, I navigated back to the list I was building. Pasting the values that you see in column labeled “ModulePath”.
So at this point, I have a complete listing in column A of all of the Interfaces used for this PI Server Collective, “R2-PI”. Not very useful, by itself, but this provides the basis for gathering data for our list.
So, let’s utilize the various tools available to us to populate our columns. Our first victim is InterfaceNode, in column B. There are actually several ways to get that tidbit, since it is present in the ModulePath in column A, the last segment, when parsed with “\”.
Continuing on, while skipping the “UFOTags” column for now, we move to the “Binary” column. Here we will use a combination of Concatenation and VLOOKUP.
- How to: Using Nested Functions and User Defined Functions t
- Since the root path is always the same length (24), we could use this formula in B28: =MID(A28,25,FIND("\",MID(A28,25,99),1)-1). <– the Find function here is a “Nested Function” within the third argument of the Mid function
- Explanation of arguments: Explanation of arguments: Since we know the values of interest all begin with the string and fixed length, LEN("\\R2-PI\%OSI\Interfaces\")=24, we can begin our search at character 25, seeking the position of the next "\", subtracting 1 from the length to exclude the "\". If the string was not of fixed length, we would have to nest additional “LEN” functions where we presently use “25”.
- Alternatively, we could create a User Defined Function, outlined in this post
- By creating a module for our workbook, in VBE, creating “Module1”, and pasting the UDF’s custom code from the linked post, we can use the following in cell B28: “=mySplitter(A28,"\",5)”
So, looking at E1, you see that I have typed “||InterfaceBinary”. This is the attribute that we want to extract from the MDB Import, for each of the interfaces in column A.
So, we need the contents of A28 appended with the contents of E1, using the & operator, like so: $A28&E$1. Note the position of the $ signs in the concatenation. $ indicates absolute references, as in “absolutely column A”, and the row is relative to the formula location. Meaning that we can fill right and the formulae with always use column A. Likewise, we can fill down, and the formulae with always reference $1, row 1.
We will nest this function inside an argument of the VLOOKUP function.
So, selecting E28 with our mouse, and typing =VLOOKUP( and then clicking “fx” on Excel’s formula bar will present the dialog below (fx is circled red in Screenshot).
Lookup_value = what to “find” in the Table Array (it searches only in the 1st column of the Table_Array, returning only the first matching entry) You can see the use of &, in the first argument in the screenshot’s dialog box.
So, in the screenshot, our Lookup_value = \\R2-PI\%OSI\Interfaces\R2-INT1\RDBMSPI1||InterfaceBinary
Table_Array = where to look for the value. Note the format of this argument, it can be drag-selected, if you click the icon at the right edge of the field. Note that you will have to manually add the $, so that you can filldown and fillright. The rows from the mdb import have an absolute address. Filling down, without absolute cell references would omit rows from the top of the list… You can also toggle between absolute and relative references, by selecting them, in the formula bar or a dialog, and using the key, repeatedly.
Note that the first column is B, where we would our Lookup_value = \\R2-PI\%OSI\Interfaces\R2-INT1\RDBMSPI1||InterfaceBinary
Col_index_num = which column of the Table_array do you wish to return if Lookup_Value is found in column 1 of Table_array
This is the column in the array, not the sheet. In my array, column 19 is the “PropertyValue” column.
Range_lookup = False means find an exact match. I almost never use True for this argument, but it can be useful on occasion.
After completing the dialog with , the formula is usable in the other columns/ rows with the use of filldown and fillright, with exceptions for columns C, D, G. and I. Note that row 1 of these columns are blank. Other methods are used for these.
In the interest of brevity, I won’t go as deeply into the details of columns C, D, and I, other than providing my formulae here:
C28 is =IF(ISNA(O28),"none",E28&K28&"_UFO2_State_"&O28)
O28 is out of view in the screenshot, but it is a lookup for “||Arguments|/UFO_ID”
D28 is =IF(C28="none","none",PICurrVal(InterfaceList!$C28,0,InterfaceList!$H28))
So, if the UFO tags are specified in the MDB, I use DataLink to show me the current values… if the interface running?
I28 is =HYPERLINK("\\"&B28&"\"&SUBSTITUTE(R28,":","$",1),"Explore Folder")
R28 contains the “||CurrentInstallationPath”, and since I am an admin on the remote machine, I can access this through the administrative share, replacing the “:” with a “$”
· Tip: System overload
Sometimes I find a spreadsheet behaving sluggishly, due to the calculation burden that I have created. With larger lists, and more and more functions, the spreadsheet will tax the CPU as it recalculates. As a lot of the functions above have already served their purpose, they can be replaced with the values which have already been calculated or retrieved. So, in these cases, copy those cells containing functions and replace, using Copy > Paste Special > Values. This will replace the functions with their static values unburdening your CPU from the unnecessary.
For those of you that have been eyeballing the Version column and the CommandButton… I saved it for Part IV.