A Macro to find missing serial numbers in a column

[Guest]

In a column, serial numbers are typed as a reference. For example from 1 to
173. How ever actual number of　raws is 175 (obvious by checking raw numbers).
2 numbers are missing. Let say for example 17 and 56 are not typed.
What is the easiest way to find these raws?
Is there any written MAcro, to find these raws?

 

[Guest]

One option using non-array formulas ..

Assume source numbers are listed in A2 down
(A1 assumed empty or containing a text col header)

Using 2 empty cols to the right, eg cols E and F

Put in E2:
=IF(A2="","",IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW()))
Copy E2 down by 175* rows to E176
*this number is assumed known

Then place in F2:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW(A1)))-1)
Copy F2 down until "blanks" appear
The missing serial numbers will be extracted at the top in col F

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Dear Max
I viewed your site.
It seams that you have put vast amount of valuable information in your site.
However there is no indexing or any reasonable way to find what the viewer
are looking for?
Do you have any comment on how to search your site for the info I am looking
for?

 

[Guest]

Dear Max
I studied your commands carefully.
I have following question:
Command IF(ISNUMBER(MATCH(ROW(A1);A:A;0));"";ROW())
and IF(A3="";"";IF(ISNUMBER(MATCH(ROW(A2);A:A;0));"";ROW()))
basically return same result. So I couldn't understand the reason for first
IF (IF(A3=""...)
The structure of this first IF is not clear for me. Is it possible to
explain its functionality? Thanks

 

[Guest]

Thanks for raising this.

Just realized that the earlier

Put in E2:
=IF(A2="","",IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW()))

should have been *without* the front check for empty cells in col A, viz.
teh part: IF(A2="","",...) as this would have returned wrong results if
indeed there were empty cells in col A

It should just be, like you mentioned, in E2, copied down by 175 rows:
=IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW())

---

 

[Guest]

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.

CTRL+Down within col F's filled formula range will simply bring you right
down to the end of the formula filled range. That's how it works. You have to
scroll down manually.

---

 

[Guest]

... you have put vast amount of valuable information in your site.
However there is no indexing or any reasonable way
to find what the viewer are looking for?
Do you have any comment on how to search your site
for the info I am looking for?

First, thanks for your comments. It's really just a free download archive
for the excel sample files which complement some of my responses in the excel
newsgroups. I've tried to front as much info as possible in the download
lists' filenames/file descripts itself, working within the max # of
characters allowed for by the free filehost (savefile.com) for filenames/file
descripts. But rest assured that full details are provided inside the files
-- as mentioned in my archives' welcoming message <g>.

---