Dear Max
Thanks for your explanation. I am trying to digest your solution.
Before entering details, I have a simple question.
I used Ctrl+arrow key in F col to reach the row which is reported as
missing, but it doesn't stop at row 116 and directly goes to end of column
but there is a number (116) in that row and ctrl+arrow just ignores it. I
can' explain to myself why this happens.
I really appreciate your talent and Excel knowledge regarding nice solution
just by combining simple commands to generate powerfull ones.
I will write later after completeing a practice of your commands and also I
will give a comment about your site which just viewed.
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Max" wrote:

You're welcome, Rasoul!

Some key clarifications, as requested:

ROW(A1) is used as an "internal" incrementer within the formulas in cols E
and F. It simply generates the series: 1,2,3 .... as we copy the formula
down. To see this happening, just put in any starting cell, say in K2:
=ROW(A1), then copy K2 down.

In the criteria col E, the core MATCH(ROW(A1),A:A,0) is simply a way for us
to "internally" generate the complete series:1,2,3,... 175 as the lookup
values to exact-match with what's in col A as we copy down. ISNUMBER is then
wrapped around the MATCH's returns (which are either numbers or #N/As,
depending on whether there's a match found) to yield a TRUE or FALSE for use
within the IF construct. Essentially when we copy E2 down, we are just
simultaneously checking & flagging out missing serial numbers in col A (where
MATCH = #N/A) via arb row numbers.

The core part in col F is essentially just the SMALL part viz.
we could have used in F2: =SMALL(E:E,ROW(A1))-1
then copy down until #NUM! appears to extract the desired results
As in col E, ROW(A1) is used as the internal incrementer here for SMALL to
extract the smallest value from col E, then the 2nd smallest, 3rd smallest,
etc as we copy down, The "-1" part is just an arithmetic adjustment to the
values returned by SMALL to produce the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khoshravan" wrote:
Dear Max
Thanks for your nice and talented solution.
It gives 2 missing numbers so I understand that my colleague has typed 116
three times for 114 and 115. Also 2 other number numbers are reported which
is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
issue beyond my need.
But more interestingly I would like to understand the logic beyond your
solution. Although your solution is two simple combination of commands but it
seems very complicated for me and beyond my knowledge to understand. If
possible please give some hints what these commands do. Thanks a lot.
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan