When analysing data in a Microsoft Excel spreadsheet, there are two things that I find I typically need to do:

1. Identify duplicate rows
2. Find matches against a lookup table

This article explains how to do these two things in excel using formulas and functions.

You might be thinking, "bah!, I'm a developer why do I need to do anything in Excel?". However I recommend you get your Excel skills up to scratch — there are lot of powerful things you can do with it. Plus all the users out there will be using it, so you might as well know a bit about it.

Identifying Duplicate Rows

Say you have the following list of operators:

How do you identify if a column (e.g. operator IDs) has duplicate values? To start with, you can sort the data in that column. This can help you see patterns of repeating data. However to be accurate, you should use COUNTIF function:

=COUNTIF(range, criteria)

The range is your data column and the criteria is the cell. the COUNTIF function returns a count of the number of matches, so if you have more than one in a column (e.g. A:A) then you have a duplicate.

So in the above example, you would create a second column with formulas as shown (duplicated column). Note that I'm showing the formula and the result, but all you would see in Excel is the result in the duplicated column.

Notice that the two JOHNSP rows return true, indicating that they are duplicated. Obviously, in this small example you could just spot the duplicated rows but try doing that with 5000 rows. A filter on the duplicated column for true values would show you just the duplicated rows.

Note that in Microsoft Excel 2007 there is a Remove Duplicates button on the Data ribbon. This is handy if you just want to clear out duplicates, but not so useful if you want to identify the duplicated rows.

Finding Matches against a Lookup Table

Say you have the following list of operator IDs and roles along with a lookup table of roles you want to find in amongst the list:

How would you find which of the operator IDs have the roles listed in the lookup table at the bottom (either Employee or PeopleTools)?

In this case, use the MATCH function. Note you can also use the VLOOKUP function, but I find the MATCH function easier.

The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

The parameters you need to specify are the lookup value (the cell), your lookup array (lookup table) and the match type. Match types can be 0 (exact match), -1 (less than), and 1 (greater than).

MATCH returns the row number that matched in the lookup table or #N/A if there is no match. Typically you want to return a boolean result like true or false or yes or no, so you need to encapsulate MATCH with the ISNA function which returns true when the result is #N/A and false when it isn't and then wrap that in an IF statement to return your desired result (e.g. NO_MATCH or MATCH):

=IF(ISNA(MATCH(lookup_value, lookup_array, [match_type])), "NO_MATCH", "MATCH")

So for the above example, you would create a MATCH column and use the formulas as shown:

Note the dollar signs ($) around the lookup array ($A$18:$A$19) in the MATCH function. This is important as it prevents the values from auto-adjusting (incrementing) as you perform an auto-fill for all rows in the column. That way the location of the lookup table stays static (cells A18 and A19) as your formulas are adjusted by the auto-fill.

Comments