List Missing Numbers Without duplicates and blank cells

[PaulaGon]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]BD1[/TD]
[TD]BD2[/TD]
[TD]Missing in BD2[/TD]
[/TR]
[TR]
[TD]001-001-001[/TD]
[TD]001-001-003[/TD]
[TD]001-001-001[/TD]
[/TR]
[TR]
[TD]001-001-001[/TD]
[TD]001-001-004[/TD]
[TD]001-001-002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001-001-002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001-001-003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001-001-004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Kind regards,

Thanks in advanced

 

[Eric W]

Put this in C2:

=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]

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]

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]

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]

Hello Eric W,

In the cell "C2" I put the formula:

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]

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

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BD1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BD2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Missing in BD2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-001[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-004[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-002[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]001-001-004[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[PaulaGon]

Hello Eric W,

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

A thousand thanks for your help!!!

 

[Eric W]

Glad we could help!