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):

BD1BD2Missing in BD2
001-001-001001-001-003001-001-001
001-001-001001-001-004001-001-002
001-001-002
001-001-003
001-001-004

<tbody>
</tbody>

Kind regards,

Thanks in advanced
 

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

MrExcel MVP, Junior Admin
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),"")
Kind regrads,
 

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!!!
 

Some videos you may like

This Week's Hot Topics

Top