Deriving list of missing serial numbers

**cimmind** · 04-13-2010, 09:08 PM

Hi. I have an inventory of items numbered 1-1000. A physical verification was done and the serial numbers of the items found present were noted. I have entered the list into excel and used sort command to arrange in ascending order.

Is there a way to formulate a second column having a list of the serial numbers of missing items. In essence the list has to be derived from '1-1000' minus the first column.

Thanks in anticipation.

**cimmind** · 04-13-2010, 10:04 PM

I have found one working solution by going through other posts. But it involves creation of two extra columns. The solution involves using ISNUMBER and MATCH functions.

Column A = Serial numbers found present
Column B = Written list of numbers 1 to 1000
Column C = Formula =ISNUMBER(MATCH(B2,$A$2:$A$1001,0)) and dragged down over column
Column D = Formula =IF(C2=TRUE,"",B2) and dragged down

Column D output gives the numbers missing in the column A array.

Is there a better method possible?

**DonkeyOte** · 04-14-2010, 04:14 AM

The way you have done it (helpers) is the optimal approach I would say if not necessarily as elegant as you would like...

You could if preferred get rid of D and just use C and given you say A is sorted you could use a slightly more efficient approach:

C2: =IF(LOOKUP(B2,$A$2:$A$1001)=B2,"",B2)
copied down

You can do without helpers using Arrays but it wouldn't be (anywhere near) as efficient as the above.

**DonkeyOte** · 04-14-2010, 04:22 AM

I should add - that by Array I meant in terms of returning the list in order and without gaps.

If you were happy to have the gaps you could forgo the helper column B and just use:

B2: =IF(LOOKUP(ROWS(B$2:B2),$A$2:$A$1001)=ROWS(B$2:B2),"",ROWS(B$2:B2))
copied down to B1001