# Thread: Finding the "missing" record in a filter: Excel 2010

1. ## Finding the "missing" record in a filter: Excel 2010

I have a field named OriginalName that contains 4,000 text records in the form of dc32e94d2b9632a7 in Column A. I set a criteria field with 120 records with similar names. My filter found 119 names among the 4,000. Is there a relatively easy way to find which of the 120 records was not among the 4,000? Thanks.

2. Jimmy,

You could place a Countif(....) next to each of the criteria records referencing the criteria to the left and the range of the 4000 records then filter the sumifs for zeros. You only need to do the first sumif them drag it down, just make sure your range of 4000 records uses absolute references or is a named range. HTH

3. Originally Posted by RetiredGeek
Jimmy,

You could place a Countif(....) next to each of the criteria records referencing the criteria to the left and the range of the 4000 records then filter the sumifs for zeros. You only need to do the first sumif them drag it down, just make sure your range of 4000 records uses absolute references or is a named range. HTH
I think that you have something here, but I' m confused. I attached my spreadsheet. In the field next to my criteria (H2), I entered a Countif. So, Countif(B2:B3813,H2). I copied the Countif down to the end of the criteria field. I then just copied the result of the Countif to another column, pasted as values, and sorted. Well, I have three zeros, when there should be one. I hope I'm not imposing on you too much. Thanks!

4. Jimmy,

Your initial formula was incorrect. It should have been: =COUNTIF(\$B\$2:\$B\$3813,\$H2)

However, when I do that and drag down they all come up with a value of 1. Which Row is supposed to not match?

If I put in a random number at the bottom of the list it returns a 0 as it should. HTH

5. Thanks.

>Which Row is supposed to not match?

That's my question. There are 120 records in my criteria field. Of those, an advanced filter locates 119 of the criteria in the corresponding (B) field. I want to know whch of the 120 criteria is not in the field. You can run the filter if you wish and see.

6. Hi Jimmy

As RG says, your formula did not use the absolute range for the counting i.e. you needed \$B\$2:\$B\$3813 in your formula before copying down.
You would then find that there were NO missing values.
I have attached a copy of your file with the corrected COUNT formula.
I also put in a MATCH formula, which I like better, as it tells you what row number the entry was found. (If an entry isn't found, it will show a result of #N/A for the row number)

zeddy

7. Jimmy,

You are correct the Extract only returns 119 records.
This item [ 7a9d2385c81f330 ] shows up twice in the Criteria list.
I found this by copying Zeddy's match list to another column as values then sorting that column and eyeballing it. HTH

8. Ah, that's the problem! It's a long story, but the 120 records are file names, and there are 120 unique files, by hash value. I just have two files with the same name, but different hash values. You guys rock!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•