Spy Journal 3.0 Filtering the web for you

The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.

What I want to do is highlight a useful way to use the IF function.

I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.

Obviously I have obfuscated the email addresses in this example for privacy reasons.

The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.

Then I added a simple filter to hide the correct results and only display those with differences.

I use this technique many times a week in comparing data lists.

Data Filters are a very powerful way of analysing tables of data in Excel. Put simply they are a way of reducing the table down to just the items you want to see based on the selections you choose.

For example a simple table like this one, may have several hundred or thousand rows of data. However there may be only a few lines you are interested in. For example you may be only interested in people under the age of 18 or sales amounts that are negative (indicating a refund) or only see sales of red Ferraris.

Turning on the Filters

In Office 2003 Turn on the filters by selecting any cell in your table (contiguous section of data) and selecting Data, Filter, Auto Filter from the menu. (Keystroke ALT D F F)

The arrows will show up as in the next picture.