The Case of the Multiple Lists


I know that we talked a lot about what to do if/when we have multiple lists from multiple sources, and we have to reconcile them. That depends variably on which list I can believe, if any.

I Know One is Right

In my case, in class, I could "believe" the district list. So I would use a MATCH function, taking the student ID from the District list, and match once against the State list, and then a second time against the School list.

So my functions might look like this:
=MATCH(A2,'School List'!A$1:A$500,0)

I would then either get a row number, showing that it was found, or an N/A error, showing that it wasn't.

If it's not in either list, I'd want to know that.

That's what the third column is for.

My formula there would use two functions: ISNA, which asks, "Is the result of the formula #N/A?" and the AND function, which asks, "Are both conditions met?"

The formula assumes that the State match is in D2, and the School match is in E2:

The result looks kind of backwards: The ones that were found in one or the other show FALSE, but the ones found in neither show TRUE.

You can reverse the sign with the NOT function, so that TRUE means found, FALSE means not found:

Think of it like reversing the sign of a number by multiplying by -1. It's weird, but it works.

No Single One is Right

For starters, what I would do is copy each of the respective lists of data into a separate worksheet.

What happens next depends on my version of Excel.

If I have tabs across the top of my screen, I'll click the Data tab, then click Remove Duplicates. The Remove Duplicates dialogue allows me to get as specific or as narrow as I want. Generally speaking, I'll use the Student Number as my basis; if it shows up more than once, remove the row.

If I have an older version of Excel that doesn't have tabs (Excel 2003 and before for the PC, Excel 2008 and before for the Mac), then I'll work around it another way. Go to the Data menu, choose Filter, then choose Advanced Filter. Check the box next to Unique Records Only.

Copy the resultant range of cells, and paste them into a new sheet.

Then, on that new "master data set" worksheet, create one column for each of the original sheets. In class, we had the State, District, and School lists, so I would create three MATCH functions, with one matching against each of the three lists.

Then, the logic becomes the same as before; I'd use multiple ISNA functions to see where' it's missing.