List Missing Numbers Without duplicates and blank cells

PaulaGon

New Member
Joined
Jan 31, 2017
Messages
16
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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>
 
Upvote 0
Hello Eric W,

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

A thousand thanks for your help!!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top