## Archive for September, 2013

## Some Excel Help

Checking column values against each other in Excel

Lo, what to do when faced with the question: How do I check to see if a value in one column shows up in another column in Excel?

There is an easy formula to check this. Well, there are two, but they’re really the same thing; it’s just that one is pretty, and the other: not so much.

The first (pretty) formula is a little more complex, mainly because it’s backwards:

=IF(ISERROR(MATCH(A1,B:B,0)),” “,”WE HAVE A WINNAAHHH!!!”)

This formula says this: If there is an error when I try to match cell A1 to column B, do nothing; if, however, there is no error, say “We have a winnaahhh!!!”

The reason that I say it’s backwards is because the first set of quotes (the empty ones) says what to do if the value is true (i.e., there is an error because there is NOT a match), and the second set of quotes says what to do if the value is false (i.e., there is NOT an error because there is a match), and we generally think that if we’re looking for something, the fact that it’s there should make it true.

The second (easy) formula is a little uggo, mainly because it fills most of a column with this: #N/A.

=IF(MATCH(A1,B:B,0),”yes”,”no”)

This formula says this: If there is a match for cell A1 in column B, say “yes”; if there is not a match, say “no”. NOTE: the “say no” part is irrelevant because if there is no match the formula will return an error (viz., #N/A).

This formula seems more “forward” in that we’re looking for a match, and it will say “yes” if the match is true.

FYI: you can replace what you want the formula to do when it finds a match with many things. For example, if you want to make a list of repeats, you can replace the “we have a winnaahhh!!!” (or the “yes”) with A1 (no quotes). This will copy the value in A1 into the new cell.

Counting up those bad boys.

Now you might be thinking: How do I determine the number of repeats in columns in Excel?

Now that you’ve identified duplicates, you may want to see how many there are. This is pretty simple, too.

=COUNTIF(C:C, “yes”)

This is assuming you’ve placed the formula in the above section in column C. The equation says this: Include a cell in the counting if it says “yes”.

The last part can be changed to include variables, so let’s say it’s a list of emails; you can use this:

=COUNTIF(C:C, “*@*”)

This will count the number of times the at symbol shows up. Use the * symbol to search for an unknown string and the ? symbol to search for one missing character. For example, suppose you’re searching for a name beginning with “Pau”; “Pau*” will return names like Paul, Paula, Pauline, and Paunchy von Puncherson; whereas, “Pau?” would only return Paul.