# List Missing Numbers Without duplicates and blank cells

#### PaulaGon

##### New Member
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

<tbody>
</tbody>

Kind regards,

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Eric W

##### MrExcel MVP
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.

#### PaulaGon

##### New Member
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!

#### Joe4

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.

#### PaulaGon

##### New Member
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,

#### Eric W

##### MrExcel MVP
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.

#### PaulaGon

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

#### Eric W

##### MrExcel MVP
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:

ABCD
1BD1BD2Missing in BD2
2001-001-001001-001-003001-001-001
3001-001-001001-001-004001-001-002
4
5001-001-002
6
7001-001-003
8001-001-004
9

<tbody>
</tbody>
Sheet7

Array Formulas
CellFormula
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),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

#### PaulaGon

##### New Member
Hello Eric W,

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

A thousand thanks for your help!!!

1,089,483
Messages
5,408,526
Members
403,212
Latest member
Rvbicon