Excel - Useful tricks

Most of the people use excel to calculate some very basic statistics. Sums, Medians, Averages ....
But Excel can do much more and can help also IT admins, below are few commands and procedures I learned over the time and which helps me with day to day work.

Parsing the input

I'm using a lot of scripts to generate data which I then need to report to someone else. Nice example are my connectivity testing scripts those are generating CSV output which I ussualy forward to file. For example here I have the CSV output of my test_icmp script (testing if devices in the -inputfile respond to ping)

PS Microsoft.PowerShell.Core\FileSystem::\\NSA320\public\scripts\connectivity-testing> .\test_icmp.ps1 -inputfile .\devices2test.txt
IP,ICMP
judo.local,Success
nas.local,Success
nsa320.local,Success
brother.local,Success
printer.local,Exception calling "Send" with "1" argument(s): "An exception occurred during a Ping request."
heating.local,Exception calling "Send" with "1" argument(s): "An exception occurred during a Ping request."
turris.local,Success

If I forward the output to csv file for example:

.\test_icmp.ps1 -inputfile .\devices2test.txt > icmpTestResults.csv

I can open it in excel. I can also copy and past the output to excel.
Now to parse it and get the IP in column A and ICMP in column B

• Mark whole A Column and go to Data -> Text to columns Choose Delimited 

• Set Comma as delimiter and click Finish

TEXTJOIN

The command joins the fields and create one text string which is connected by the character(s) we specify.
For example let's say I got the list of devices in excel and I have Hostname in column A and domain in B and I want to get in C the FQDN (hostname.domain) to get it I can use following formula:

=TEXTJOIN(".";TRUE;A2:B2)

first parameter (".") is delimiter the character or even string connecting the text in fields
second parameter is say if we want to ignore empty fields
third is range of fields to join

CONCAT

Concatenate (CONCAT) I'm using where I need to generate text which is combination of static and dynamic text.
Let's say I have a config file which shall contain a list of unix commands I can use and each command shall be on one line starting with key word "command="

=CONCAT("command=";A2)

COUNTIF

Is useful to create simple statistics, or to verify that something from one list is in another list.

=COUNTIF(C:C;"TRUE")

This count how many lines in Column C is TRUE

=COUNTIF(C:C;"*")-1

This count how many lines in Column C contain any text. -1 Because header is also count.

In sheet "List of Linux Servers" I have my linux servers and I want to check if they all are in summary sheet.

=COUNTIF(Summary!A:A;'List of Linux Servers'!A2)

This actually counts howmany times text/value in cell 'List of Linux Servers'!A2 is in sheet Summary column A.
You can also verify if there are duplicates (obviously)

I also want to see in summary if device is linux. So I can "reverse" the formula.

=COUNTIF('List of Linux Servers'!A:A;Summary!A2)

VLOOKUP

I often need to compile summary from several tests so what I ussualy do is run my testing scripts, for example test_icmp and test_tcp.
I put the results to the sheets (ICMP Results, TCP 22 Results) and one summary sheet which contains the list of IPs or Hostnames I used as inputfile for my scripts. And then two columns which will use formula to get corresponding ICMP and TCP result.

=VLOOKUP(A2;'TCP 22 Results'!A:B;2;FALSE)
and
=VLOOKUP(A2;'ICMP Results'!A:B;2;FALSE)

• The first argument is what I'm searching for (the value in first Column)
• The second argument is Where to search (sheet and range) but it actually goes through the first column in range
• The third argument is which value to display as result, in our case 2 which means we want to display second column value in range, hence column B.
• The fourth argument is if we are looking for Exact match (FALSE) or just partial match (TRUE)

AND, OR, IF

I use logical expresion to find out if sertain conditions are met and IF to just make output nice. (but IF can be used in moer clever ways). For example to be able onboard devices to some monitoring tool, I need to have all connectivity and access in place. That can be checked by scripts, but I have very simple to test one thing at time ;-). I use AND in example to combine two conditions to see if both ICMP and SSH works hence I can onboard the device.

=IF(AND(B2="Success";C2=TRUE);"Ready";"Not Ready")

Let's make it more complicated and add WMI test for windows based devices. Now if device is Linux we test B and C column, else we check B and D

=IF(E2=1;AND(B2="Success";C2=TRUE);AND(B2="Success";D2=TRUE))

Filters

Filtering of data is very usefull to get what you need, but be very carefull, the more complicated filtering you try to do the better chance you filter out something you don't want to filter out. Especially keep in mind that two filters (filters on two columns) works as AND. Hence if you if you need OR condition you are not able to achive it with filters.

Substring

Most IT (programmers, admin which can script) knows function substring or its variant. But in excel you don't find it instead you can use, LEFT, RIGHT. To get specified number of characters from beginning(LEFT) or end(RIGTH) of string.

=LEFT(string;number of chars)

However sometimes it's not fixed but you want sting up to some character(s). Favourite examlpe hostname versus FQDN. I got the FQDN and need hostname. So I need substring from left to fisrt dot. Function FIND returns the position (index - starting from 1) of character(or substring).

=FIND("what I search";"in which string")

Now le's combine both to get the hostname from FQDN in row A

=LEFT(A2;FIND(".";A2)-1)

EXAMPLE: Crafte RegEx

I've got list of FileSystems to monitor and need to craft RegEx for monitoring tool template which would tell the tool to monitor only the FileSystems from that list.
In the tool that "monitored object" starts with FileSystem\ then is mount point and then \Use%

Hence if wanna monitor /opt, then need something like this:

/^FileSystems\\/opt\\Use\%$/

If need /opt and/or /usr then it would be

/^FileSystems\\((/opt)|(/usr))\\Use\%$

But if there is more of them it's better to put them in excel (column A in this case starting by A2) and use nice excel formula like this:

=CONCAT("/^FileSystems\\((";TEXTJOIN(")|(";TRUE;A2:A100);"))\\Use\%$/")

EXAMPLE: Check that FQDN match the reverse DNS Lookup

One of the script for connectivity checking I mentioned earlier is to do DNS Lookup and reverse DNS Lookup. It's useful to check that if I can resolve all hosts which customer provides me and also verify the reverse resolution works. However it's also important to check (at least for me and tools I'm administrating) that reverse resolution correspond to given name.

So I run two scripts one to resolve to IP and then I've took those IPs put them to new file and use it as imput to run the script with -reverse switch:

PS C:\Users\j.kindl\Documents\scripts\connectivity-testing> .\test_dns.ps1 -inputfile .\devices2test.txt
Name,IP
judo.local,10.0.0.37
nas.local,10.0.0.33
nsa320.local,10.0.0.33
brother.local,10.0.0.39
printer.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
heating.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
turris.local,10.0.0.1
yun.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
evolve.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
tv.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
dvb-t.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
switch.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"

PS C:\Users\j.kindl\Documents\scripts\connectivity-testing> .\test_dns.ps1 -reverse -inputfile .\ip2test.txt
Name,IP
10.0.0.37,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.33,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.33,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.39,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.1,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
PS C:\Users\j.kindl\Documents\scripts\connectivity-testing>

I create in Excel two sheets one with results resolution of FQDN to IP (FQDN2IP) and one with results of resolving IP to FQDN (IP2FQDN). In FQDN2IP added two columns one is using vlookup to get the FQDN based on IP from sheet IP2FQDN.

=VLOOKUP(B2;IP2FQDN!A:B;2;FALSE)

Second column to Check if original FQDN match the reverse resolution. It just compares Colum A and C.

=EXACT(A2;C2)

UPDATE:
EXACT - is case sensitive and as such can mark false even the original FQDN matches the reverse resolution, they just differ in Upper/Lower cases.
To over come this you shall rather use folowing formula:

=AND(A2=C2)