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
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
9,294
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
Joined
Jan 31, 2017
Messages
16
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
Joined
Aug 1, 2002
Messages
52,092
Office Version
365
Platform
Windows
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
Joined
Jan 31, 2017
Messages
16
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
Joined
Aug 18, 2015
Messages
9,294
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
Joined
Jan 31, 2017
Messages
16
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
Joined
Aug 18, 2015
Messages
9,294
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
Joined
Jan 31, 2017
Messages
16
Hello Eric W,

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

A thousand thanks for your help!!!
 

Forum statistics

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

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top