How to create a matchkey

by | Dec 19, 2016

First off, let me explain what a Matchkey is. It’s an alphanumeric (letters and numbers) string (line) that is unique to each record (supporter/person/constituent) in a data file. It is used to match against a matching string in another data file so that the two can be joined together.

Imagine you have two files. The first file is a list of supporters; the second is a list of gifts. The easiest way of matching these two would be on supporter URN. This is less of a Matchkey and more a primary key, which is a database term, used to describe the way of linking tables in a relational database. And that’s as much as you need to know about relational databases.

Now imagine your two files don’t have a unique number, what if they only have a first name, a surname, an address line and a postcode.

– You can’t match on postcode because you can get more than one person at a house, or more than one household in a postcode.
– You can’t match on address lines for the same reason.
– You can’t match on first name because it would try and match all the John’s together or all the Linda’s.
– And the same with the surnames.

This is now a problem. You have all the information but you can’t join it together. Whilst you can’t match using each individual element, imagine if you joined them all up together. This is called a Matchkey.

The process is similar whether you are using Excel or Access. I’ll describe the method for Excel, but if you want a suggestion for doing this in Access then just give us a shout.

– Start by deciding what combination of fields would make a unique key if they were put together.
– I would normally use first name-lastname-addressline1-postcode.
– Now insert a new column into your data, or use the very last one. Enter the function =Concatenate(,,,)
– Create this field in both of your data tables.
– Now you can match one table to the other using your Matchkey and whatever lookup method you would normally use: vlookup, match and index etc.

This is also really useful for deduping a file. If you only have one file and you believe you have duplicates, create the additional Matchkey field, exactly as described above. Next select the entire data range and sort using the Matchkey field. An easy way of identifying the dupes is to create another new field which compares one Matchkey to the one underneath (or above) using the =Exact function i.e. =Exact (A4,A5) – if the Matchkey in A4 is the same as the one below it in A5 the function will return TRUE, otherwise it will return FALSE.

And if you want to get really fancy, and to make it even easier to see the true’s, use some conditional formatting on the =Exact column that highlights those that have returned true (I like a yellow background with bold red writing – it ensures you don’t miss them).

If you need any help with any of the above, then please give the PtG team a call. We’re always happy to demonstrate exactly how nerdy we really are.

Share This