Results 1 to 10 of 10

Thread: List Missing Numbers Without duplicates and blank cells

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default List Missing Numbers Without duplicates and blank cells

    Hello


    I need some help with a problem that I am trying to solve. I hope someone can help me.

    I have a list of values in one column (A:A), that can have blank cells in the middle of the range and also repeat values, and I need to compare them with a second list of values of another column (B:B) and extract a unique list of the missing values (unique values that are in column A but not in column B):

    BD1 BD2 Missing in BD2
    001-001-001 001-001-003 001-001-001
    001-001-001 001-001-004 001-001-002
    001-001-002
    001-001-003
    001-001-004

    Kind regards,

    Thanks in advanced

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Put this in C2:

    Code:
    =IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C2,$A$2:$A$10)=0,IF(COUNTIF($B$2:$B$8,$A$2:$A$10)=0,ROW($A$2:$A$10)))),1)-ROW($A$2)+1),"")
    change the ranges to match your sheet, and confirm by pressing Control+Shift+Enter. Drag down as far as necessary.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jan 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Hello Eric W,

    Thank you a lot for the quick response. I used the formula in my worksheet but it give me only "0".

    I am going to work the data in column "A" to avoid having blank cells in the middle of the range and also repeat values.

    Thank you for your help anyway!

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    I used the formula in my worksheet but it give me only "0".
    Did you enter it as an array formula, like he instructed?
    and confirm by pressing Control+Shift+Enter.
    You must enter the formula using Control+Shift+Enter instead of just using Enter.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jan 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Hello Joe4,

    Yes, after inserting the formula I used the Control+Shift+Enter...the formula got the symbols "{}" and after that, I dragged down the formula

    Kind regards,

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Could you post what you changed your formula to? If it's returning a "0", that indicates it's trying to point to an empty cell. This should not happen, since it specifically checks for empty cells. When I first tested it, I got a 0 as well because there was a typo in it I had to fix. Maybe something similar is happening that we can spot.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Jan 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Hello Eric W,

    In the cell "C2" I put the formula:

    Code:
    IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C2,$A$2:$A$10)=0,IF(COUNTIF($B$2:$B$10,$A$2:$A$10)=0,ROW($A$2:$A$10)))),1)-ROW($A$2)+1),"")
    Kind regrads,

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Oops! Sorry, I gave you the C3 formula by mistake. Only 1 character different, but that's all it took to mess it up. Try this:

    A B C D
    1 BD1 BD2 Missing in BD2
    2 001-001-001 001-001-003 001-001-001
    3 001-001-001 001-001-004 001-001-002
    4
    5 001-001-002
    6
    7 001-001-003
    8 001-001-004
    9
    Sheet7

    Array Formulas
    Cell Formula
    C2 {=IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C1,$A$2:$A$10)=0,IF(COUNTIF($B$2:$B$8,$A$2:$A$10)=0,ROW($A$2:$A$10)))),1)-ROW($A$2)+1),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    New Member
    Join Date
    Jan 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Hello Eric W,

    After adapt you formula in my original worksheet it works exactly as I need.

    A thousand thanks for your help!!!

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: List Missing Numbers Without duplicates and blank cells

    Glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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