# 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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

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!

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.

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,

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.

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

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>

Hello Eric W,

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

A thousand thanks for your help!!!

Replies
3
Views
157
Replies
2
Views
280
Replies
6
Views
531
Replies
3
Views
232
Replies
0
Views
410

1,218,744
Messages
6,144,227
Members
450,531
Latest member
avril18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back