Finding out missing numbers

[kwfine]

Hi all,

I am using Excel 2002.
In a sheet, I have three columns of numeric data as follows:

Column A (registered numbers)
1
2
3
4
5

Column B (random input numbers)
5
4
3
3
3

Column C (absent numbers)
1
2



Description:
Numbers under Column A are registered.
Numbers under Column B are to be checked against those under Column A.
It is clearly that Numbers 1 and 2 are missing, and so they are manually written under Column C.

Question:
In this example, there are only a few numbers used, so we can quickly see that which numbers are missing.
But when the numbers become larger, say a few hundreds, then that will be difficult for human to find out which numbers are missing.
So, I am wondering how to use VB or other Excel functions else to do the
same thing (i.e. to find out which numbers are missing).

Could you help me out please?

Thanks a lot.

Kitty

[DonkeyOte]

One possible approach if we assume values start in A1 & B1

First

D1: =SUMPRODUCT(--ISNA(MATCH(A1:A5,B1:B5,0)))
gives count of missing numbers

Then

C1: 
=IF(ROWS(C$1:C1)>$D$1,"",SMALL(IF(ISNA(MATCH($A$1:$A$5,$B$1:$B$5,0)),$A$1:$A$5),ROWS(C$1:C1)))
confirmed with CTRL + SHIFT + ENTER
copied down

An alternative approach (not requiring CTRL + SHIFT + ENTER (though not more efficient per se)) if you were to insert a row above the first values such that what was row 1 becomes row 2 and row 1 is thus blank...

D2: 
=SUMPRODUCT(--ISNA(MATCH($A$2:$A$6,$B$2:$B$6,0)))
(as before but different range obviously)

C2:
=IF(ROWS(C$2:C2)>$D$2,"",INDEX($A$2:$A$6,MATCH(1,INDEX((ISNA(MATCH($A$2:$A$6,$B$2:$B$6,0))*ISNA(MATCH($A$2:$A$6,$C$1:$C1,0))),0),0)))
copied down

There are various other routes open to you - some simpler than the above if you can use "helper" cells, not clear.

Let us know.

[kwfine]

Thank you so much for the prompt help, DonkeyOte.
The first approach works. I have not tested the second approach though.

I have difficulty in understanding the IF syntax.
because I have never written something like that.

C1: 
=IF(ROWS(C$1:C1)>$D$1,"",SMALL(IF(ISNA(MATCH($A$1:$A$5,$B$1:$B$5,0)),$A$1:$A$5),ROWS(C$1:C1)))
confirmed with CTRL + SHIFT + ENTER
copied down

[DonkeyOte]

I have difficulty in understanding the IF syntax.

The formula is an Array - if you're not aware of arrays I would certainly suggest having a read through of Chip Pearson's page http://www.cpearson.com/excel/ArrayFormulas.aspx and also Colin Legg's tutorial as linked in my sig.

The formula:

=IF(ROWS(C$1:C1)>$D$1,"",SMALL(IF(ISNA(MATCH($A$1:$A$5,$B$1:$B$5,0)),$A$1:$A$5),ROWS(C$1:C1)))

Can be broken down & read in parts...

=IF(ROWS(C$1:C1)>$D$1,"",....)

Says, if the formulae added thus far (determined by ROWS count) exceeds the number of missing numbers (as calculated in D1) then return a Null else....

SMALL(IF(ISNA(MATCH($A$1:$A$5,$B$1:$B$5,0)),$A$1:$A$5),ROWS(C$1:C1))

The SMALL function works along the lines of below (note: colour coded to match the parts above & below):

SMALL(array,k)

So here we populate the array of values with:

IF(ISNA(MATCH($A$1:$A$5,$B$1:$B$5,0)),$A$1:$A$5)

and the above says if you can not find A1:A5 in B1:B5 then add value in A1:A5 to the array of values (else populate with a Boolean FALSE)

Using your example table of:

1	5
2	4
3	3
4	3
5	3

we end up with an array of values of:

{1,2,FALSE,FALSE,FALSE}

Because 1 & 2 are not found in Column B they are added to the array of values whereas given 3,4 & 5 are found we simply return a Boolean FALSE to the list.

So we effectively have

SMALL({1,2,FALSE,FALSE,FALSE},ROWS(C$1:C1))

SMALL will return the k smallest value from the array of values and will only consider numerical values... eg

SMALL({1,2,FALSE,FALSE,FALSE},1) -> 1 as 1 is the smallest number

SMALL({1,2,FALSE,FALSE,FALSE},2) -> 2 as 2 is the 2nd smallest number

In our case we use ROWS(C$1:C1) to determine k such that as the formula is copied down the k value increments accordingly, eg

C1: =ROWS(C$1:C1) --> returns 1 as there is only one row in the range specified

the same copied to C2 becomes

C2: =ROWS(C$1:C2) --> returns 2 as there are now two rows in the range specified

(if unclear on mixed / absolute / relative references be sure to check out XL Help files)

[kwfine]

Thank you so much for your help, DonkeyOte.

[kwfine]

I am sorry, DonkeyOte

I would like to ask how I should change the codes accordingly if the data are not placed in rows instead of columns like:


Row A (registered numbers)
1 2 3 4 5 (each number occupies a cell)

Row B (random input numbers)
5 4 3 3 3 (each number occupies a cell)

Row C (absent numbers)
1 2 (each number occupies a cell)