Combining data sets

Recently at work we ran into a situation where my coworker had extracted the names of all of the current members from a spreadsheet, and asigned access codes to them. Afterwards, we realized that we also needed the email addresses, so we had to make sure each person had both the right code and the right email address.

The IF function (about)

If.
If you’re going to the grocery store anyway, then could you pick up milk?
If I pay $1 extra every month, then my loan will be paid off a month early.
If you finish your vegetables, then you can have dessert, otherwise you’re going straight to bed.

Everyone uses “if” statements, every day. Sometimes you explicitly say “then”, and other times you skip it, but it’s still understood that the second part only happens if the first part is true. Likewise, sometimes you explicitly say what will happen if it’s not true, and sometimes you skip it because it’s obvious or irrelevant.

The IF() function is a fantastic little function, and the most basic building block for getting your spreadsheet to do some of the evaluation for you. The syntax is always the same: IF(condition,then,else) in Excel or Google Docs, or IF(condition;then;else) in OpenOffice. So the statements above could be rephrased as:

Cell name: buy_milk
Formula: =IF(Destination=”store”,TRUE,FALSE)
Translation: If the value in Destination is “store”, then it is true that you should “buy milk”. Otherwise, “buy milk” is false, so you shouldn’t do it.

Cell name: payoff_date
Formula: =IF(payment=minimum_payment+1,original_date-30,original_date)
- or -
=IF(payment=minimum_payment+1, date(year(original_date),month(original_date)-1,day(original_date)), original_date)
Translation: First version – If the payment is the minimum payment plus one, then the actual payoff date is the original payoff date minus 30 days, otherwise it’s the original payoff date. The second version is the same but a little more accurate since it subtracts one month instead of 30 days.

Cell name: Instructions
Formula: =IF(vegetables=”finished”,”you can have dessert”,”go straight to bed”)
Translation: If the value in vegetables is “finished”, then the instructions are “you can have dessert”. Otherwise, the instructions are “go straight to bed”.

Obviously these aren’t things you’d really set up in a spreadsheet. However, I do use IF a whole, lot, mostly for things like:

Checking whether two cells match each other: I use this regularly in proofreading, and it saved me a lot of work when I was doing a project verifying links for e-resources. I also used it recently when combining two sets of data that shared a unique identifier. And I use it a whole lot when trying to condense a list of things down to unique items.

Checking whether a cell contains a certain value: This is very useful when trying to verify that all citations in a paper are referenced, and all references are cited. I also use it a lot when trying to extract data from a text file with section headers.

Checking whether a cell is greater or less than a certain value: I don’t use this a whole lot, but I did use it recently when generating an image of the Mandelbrot set.

I’ll be sharing a lot of projects illustrating these things in the weeks to come, so if the examples here didn’t make sense then actual applications might.

Boolean operator functions: AND, OR, NOT

Last week when I was discussing the conditional formatting I did to highlight problem cases, I said I checked two things for the same condition. However, you can only enter one formula. So how did I do it? I combined the two things to check using AND.

Conditional formatting with formulas

The second interesting change I made in the updated library instruction spreadsheet was a type of conditional formatting that I haven’t specifically discussed before: formatting that depends on something other than the contents of the cell being formatted.

Nifty trick: Pulling just 1 year’s data

Last week I was focusing on how to use incremental formulas to determine the most recent time something was used, but in the process of adding that to the example spreadsheet I also ended up making a couple of other changes that are worth discussing.

Change 1: Limited to the current year

You might have noticed that although there were dates from January 2012 through May 2013, on the table showing classes taught by month that there aren’t any entries after May.  That’s because I’ve set it to only pull the dates from the current year.  Here’s a generic explanation as to do that.

Project: Use tracking

There’s another type of incremental formula that can be used to track how recently someone did a particular thing, or how recently a particular thing was used. These things could be almost anything, though it works best with finite sets. It could be used to track equipment usage by tracking how recently each item was used, and who used it. It could be what household chores and been done, and who’s been doing them. As an example I’ve made a spreadsheet that tracks how recently a given professor has brought their class in for library instruction. It’s a modification of the one I did as a countif() example.

Wild cards and regular expressions

If I’m going to discuss finding and replacing, I really need to also discuss wild cards. Most people are probably familiar with the concept of wild cards, in one form or another. I assume the term comes from card games, where jokers can be used to represent any other card that’s needed. People may also be familiar with the concept, if not the term, from Scrabble, where there are blank tiles that can represent any other letter tile.

Wild cards in spreadsheets (and in word processors, and in internet searching, etc.) are characters that can represent other characters – often more than just one. That is to say, if * is a wildcard then educat* could be educate, education, educating, educator, educatxyz, and so on.