Results 1 to 8 of 8
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Question 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.
    JimmyW
    Helena, MT

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    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!
    Attached Files Attached Files
    JimmyW
    Helena, MT

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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!
    JimmyW
    Helena, MT

Posting Permissions

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