# Thread: List Missing Numbers Without duplicates and blank cells Thanks:  1 Post #5236949 (1) Likes:  1 Post #5236949 (1)

1. ## 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,

2. ## 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.

3. ## 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. ## 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.

5. ## 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. ## 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.

7. ## 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),"")`

8. ## 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

9. ## 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!!!